查询全库所有表各表总记录数-PG版本

--创建函数
CREATE OR REPLACE FUNCTION public.queryTableRecord(dbname VARCHAR(300), schema_prefix VARCHAR(300))
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
  schemaQueryRec RECORD;
  tableQueryRec RECORD;
  querySql VARCHAR(900);
  insertSql VARCHAR(300);
  tmpSql VARCHAR(300);
BEGIN
  --1.创建临时表
  DROP TABLE IF EXISTS public.tmp_big_table_record;
  CREATE TABLE public.tmp_big_table_record(
    schemaname VARCHAR(300),
    tablename VARCHAR(300),
    rowcount NUMERIC(19)
  );
  insertSql := 'insert into public.tmp_big_table_record ';
  --2.生成各schema的查询脚本
  FOR schemaQueryRec IN
    SELECT
            'select ''select ''''' || SCHEMA_NAME || ''''','''''' || tablename || '''''' , count(*) from '' || schemaname || ''.'' || tablename || '' union all'' as sSql from pg_tables where schemaname = ''' || SCHEMA_NAME || '''' as query_sql
    FROM
            information_schema.schemata
    WHERE
            CATALOG_NAME = dbname
    AND SCHEMA_NAME LIKE schema_prefix || '%' LOOP
    querySql := schemaQueryRec.query_sql;
    --打印每个模式的查询脚本到控制台
    RAISE NOTICE 'notice: %', querySql;
    FOR tableQueryRec IN EXECUTE querySql LOOP
      --如果查询结果为空，则不处理
      IF LENGTH(tableQueryRec.sSql) > 0 THEN
        --去除最后一个union all
        tmpSql := SUBSTRING(tableQueryRec.sSql, 1, LENGTH(tableQueryRec.sSql) - 9);
        --拼接insert语句
        tmpSql := insertSql || tmpSql;
        --执行脚本
        EXECUTE tmpSql;
      END IF;
    END LOOP;
  END LOOP;
  RAISE NOTICE 'job done!!!';
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
;

ALTER FUNCTION public.queryTableRecord(dbname VARCHAR(300), schema_prefix VARCHAR(300)) OWNER TO artbase;


--执行函数
select public.queryTableRecord('fb09', 'db_');

--查看结果
select * from public.tmp_big_table_record order by rowcount desc;

--删除临时表
drop table public.tmp_big_table_record;