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

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.

CodePudding user response：

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.