Do a select query on all tables of PostgreSQL database using pgAdmin 4

Question

I have a database of around 90 schemas. In each of these schemas, I go to "Materialized Views" and go to a one of the views called, "product_visitor_view" and I create a SELECT script and I write this script and run it and see the results:

SELECT priority, count(*)
FROM ag_modem_01.product_visitor_view
group by priority;

However, I cannot do this for all 90 around schemas. Is there a way I can do this for all schemas and the results would be shown for each schema in a page and how can I do this?

Thank you in advance.

Answer 1

You can prepare the SQL for each schema using below query. The idea is to instead of manually writing all the query, you can use the system table pg_matviews which contains information about materialized views. Once you have the list, just need to do union all between all the rows.

select 
string_agg('select '''||schemaname||''' as schema,priority,count(*) from '||schemaname||'.'||matviewname
           ||' group by priority '||chr(10),' Union All '||chr(10)
           )
from pg_matviews
where matviewname='product_visitor_view'
and ispopulated=true; -- to filter out Views which has not been populated

Take the output from this query and run it in the Querytool.