Postgres SQL query across different schemas

Question

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?

stackoverflow.com/questions/2596670/…

user330315
– user330315

2020-10-18 14:41:07 +00:00
Commented Oct 18, 2020 at 14:41 — user330315
– user330315, Commented Oct 18, 2020 at 14:41

user · Accepted Answer · 2022-04-11 14:52:31Z

4

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.

edited Apr 11, 2022 at 14:52

user

5,4417 gold badges54 silver badges66 bronze badges

answered Oct 18, 2020 at 15:56

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

USer22999299 Over a year ago

Seems like the query workd, but raise notice 'Schema % count %', schema_name, table_row_count; was not printing anything wastrying to run change it to raise instead by same thing how can i validate the output?

Stefanov.sm Over a year ago

Pls. comment the exception line in order to see what's wrong. I edited my answer, format fits better than replace.

USer22999299 Over a year ago

Right - ERROR: column reference "schema_name" is ambiguous LINE 1: SELECT schema_name FROM information_schema.schemata

Stefanov.sm Over a year ago

My mistake. The "schema_name" variable and field had the same name. Edited my answer, changed the variable to "v_schema_name", should be OK now.

Belayer Over a year ago

I see a major problem. The exception handler. As written this is a a pure bug. If a an error occurs your routine terminates prematurely. Meaning you get the wrong answer and have no way to find out why. At a minimum log/print the error. Even better if you are not prepared to write corrective code then do not capture the exception.

|

Collectives™ on Stack Overflow

Postgres SQL query across different schemas

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related