CODEWITHSUNDEEP
sqlpostgresql
USer22999299
USer22999299

Reputation: 5674

Postgres SQL query across different schemas

We have multiple schemas, I would like to run a simple count query across schemas such as:

SELECT COUNT(col_x) FROM schema1.table WHENRE col_x IS NOT NULL

I saw that I'm able to get all the schemas with:

SELECT schema_name FROM information_schema.schemata

So by using:

set search_path to schema1; 
SELECT COUNT(col_x)
FROM table
WHERE col_x is not NULL;

I was able to run the query for schema1

The question is - is it possible to run in a loop and use the schema name as a parameter for search_path and run the query across all schemas? or any other efficient way to do so?

Upvotes: 0

Views: 2036

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13009

You will need some plpgsql and dynamic SQL for this. Here is an anonymous block for illustration:

do language plpgsql
$$
declare
 v_schema_name text;
 table_row_count bigint;
 sysSchema text[] := array['pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','public','information_schema'];
 -- other declarations here
begin
 for v_schema_name in SELECT schema_name FROM information_schema.schemata WHERE (schema_name != ALL(sysSchema)) loop
   begin
     execute format('select count(col_x) from %I.t_table', v_schema_name)
     into table_row_count;
     raise notice 'Schema % count %', v_schema_name, table_row_count;
   exception when others then null;  -- t_table may not exists in some schemata
   end;
 -- other statements here
 end loop;
end;
$$;

And btw WHERE col_x is not NULL is redundant.

Upvotes: 4

Related Questions