Postgresql如何用游标查出所有表的大小和条数，然后插入某张表？

Question

华为云的数据中台
现在有一个场景要统计数据库每日新增条数和总数据库大小
想先把所有表大小和条数插入一张统计表，加上时间，每天自动执行

Answer 1

查pg_class,速度更快,只是里面的数据是定时刷新。
对每张表去count的话,速度很慢,而且对数据库的性能造成不必要的影响。
非得要查的话,无非就是用pg_class来作为游标,然后拼接动态sql来执行插入count值的指令

---

给你一个官方提供的循环并执行了动态sql的例子吧,你只要把游标sql改了,把动态sql改成拼insert语句就行了

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

中文官方文档地址
http://postgres.cn/docs/12/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS