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?

Answer

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.