Iterate through schemas

Question

I have several schemas in my database. Most of the schemas have a table called orders. How do I iterate through the schemas that have that table and get a count of the number of records in each of those schemas for that table?

Answer 1

select * from information_schema.schemata;

see https://www.postgresql.org/docs/11/infoschema-schemata.html

---

do
$$
declare
    tableName varchar := 'xyz';
    schemaName varchar;
    query text;
    counter integer;
begin
    for schemaName in select table_schema from information_schema.tables where table_name = tableName
        loop
            query := 'select count(*) from ' || schemaName || '.' || tableName;
            execute query into counter;
            raise notice E'%  %', schemaName, counter;
        end loop ;

end $$