PostgreSQL 使用存储过程每日统计数据库所有模式下的每张表的数量

-- 创建统计表
CREATE TABLE IF NOT EXISTS schema_table_counts (
    schema_name TEXT,
    table_name TEXT,
    row_count BIGINT
);
 
-- 创建存储过程用于统计每个模式下的表的行数
CREATE OR REPLACE FUNCTION count_tables_in_schemas()
RETURNS void AS $$
DECLARE
    schema_record RECORD;
    table_record RECORD;
BEGIN
    -- 循环遍历所有模式
    FOR schema_record IN SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname NOT LIKE 'information%' LOOP
        SET search_path = schema_record.nspname; -- 设置搜索路径为当前模式
 
        -- 循环遍历当前模式下的所有表
        FOR table_record IN SELECT tablename FROM pg_catalog.pg_tables WHERE tablename NOT LIKE 'pg_%' LOOP
            EXECUTE format('INSERT INTO schema_table_counts(schema_name, table_name, row_count) VALUES (%L, %L, (SELECT COUNT(*) FROM %I))', schema_record.nspname, table_record.tablename, table_record.tablename);
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
 
-- 调用存储过程进行统计
SELECT count_tables_in_schemas();
 
-- 查询统计结果
SELECT * FROM schema_table_counts;

这段代码首先创建了一个存储每日统计结果的表schema_table_counts。然后定义了一个存储过程count_tables_in_schemas，该过程使用PL/pgSQL编写，遍历除pg_开头的系统模式和information_schema模式外的所有模式，再遍历每个模式中的表，统计每个表的行数，并将结果插入到统计表中。最后提供了调用存储过程的示例和查询统计结果的示例。