How to loop over list of schemas in POSTGRESQL

Question

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;
$$
;

https://stackoverflow.com/q/2596670/330315 – Mar 07 '18 at 10:48 — , Mar 07 '18 at 10:48

score 1 · Accepted Answer · answered Mar 07 '18 at 10:52

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

How to loop over list of schemas in POSTGRESQL

1 Answers1