0

I have access to db with a lot of schemas (each schema - separate store). And this db have not information_schema.

Next code gives me a list with over 1K schemas:

SELECT nspname FROM pg_namespace WHERE nspname LIKE 'cabinet%'

I need to count rows in some table, if it exist, for every scheme in that list. Something like:

for scheme in scheme_list:
    SELECT scheme, count(*) FROM scheme.table_i_need

Output should contain schema_name and some integer value.

So long I was not able to find the answer, please help.

UPD: Thanks to Vao Tsun, I was able to even write this loop over text array with schema names.

do $$
    declare
    m text[];
    a text[] := array[['cabinet1003052234'], ['cabinet1027326445'], ['cabinet1062828216'], ['cabinet108034857']];
    s text;
begin
FOREACH m SLICE 1 IN ARRAY a LOOP
execute format('select count(*) from %I.react_order', CAST (m[1] AS regnamespace)) into s;
    raise info '%: %', m,s;
end loop;
end;
$$
;
Improve this question
1

1 Answer 1

Reset to default
1

your approach would be more expemsive - listing schemas and then checking if table exists and then counting rows. you either rely on statistics from pg_stat_* or loop counts from pg_class, smth like:

t=# do $$
declare
 r record;
 s text;
begin
for r in (select relnamespace::regnamespace nspname,relname from pg_class where relname like 't%' and relkind = 'r') loop
 execute format('select count(*) from %I.%I',r.nspname,r.relname) into s;
 raise info '%.%: %', r.nspname,r.relname, s;
end loop;
end;
$$
;
INFO:  postgres.tb: 1
INFO:  postgres.tt: 0
INFO:  public.tt: 0
INFO:  postgres.t3: 1
INFO:  postgres.testtable: 1
INFO:  a.tt: 0
INFO:  b.tt: 0
INFO:  postgres.tT: 0
INFO:  postgres.ta: 1
INFO:  postgres.t5: 1
INFO:  postgres.tb1: 1
INFO:  postgres.tb2: 1
INFO:  s1.t: 1
INFO:  s2.t: 1
INFO:  postgres.test: 1
INFO:  public.test: 6
INFO:  postgres.t: 9904
DO
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.