遍历模式

Question

我的数据库中有几个模式。大多数模式都有一个名为orders的表。如何迭代具有该表的模式，并为该表获取每个模式中记录的数量？

Answer 1

select * from information_schema.schemata;

请参阅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 $$