如何生成PostgreSQL指定模式下每日/每月新增记录数报表？

针对你需要生成指定模式下各表每日/每月新增记录数报表的需求，分两种场景提供可行方案：

一、表已包含记录创建时间字段（推荐方案） #

如果你的表中已有created_at（或类似命名）字段记录每条数据的插入时间，可以通过动态SQL批量统计所有表的新增记录数：

1. 每日新增统计函数 #

创建PL/pgSQL函数自动遍历指定模式下的所有表，生成每日新增记录统计：

CREATE OR REPLACE FUNCTION get_daily_new_records(schema_name text)
RETURNS TABLE(table_name text, record_count bigint, stat_date text) AS $$
DECLARE
    tbl record;
BEGIN
    FOR tbl IN SELECT table_name FROM information_schema.tables 
               WHERE table_schema = schema_name AND table_type = 'BASE TABLE' LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L AS table_name, COUNT(*) AS record_count, TO_CHAR(created_at, ''DD-MM-YYYY'') AS stat_date 
             FROM %I.%I 
             GROUP BY TO_CHAR(created_at, ''DD-MM-YYYY'')',
            tbl.table_name,
            schema_name,
            tbl.table_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 调用方式 #

查询public模式下的每日新增记录：

SELECT * FROM get_daily_new_records('public') 
ORDER BY table_name, stat_date DESC;

3. 每月新增统计调整 #

只需将函数中的TO_CHAR(created_at, ''DD-MM-YYYY'')替换为TO_CHAR(created_at, ''MM-YYYY'')，即可实现每月统计。

二、表无创建时间字段的处理方案 #

如果表中没有记录插入时间的字段，需要先补充该字段并通过触发器自动维护：

1. 通用触发器函数 #

创建用于自动设置插入时间的触发器函数：

CREATE OR REPLACE FUNCTION set_created_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 批量给表添加字段和触发器 #

通过DO语句批量为指定模式下的所有表添加created_at字段，并绑定触发器：

DO $$
DECLARE
    tbl record;
BEGIN
    FOR tbl IN SELECT table_name FROM information_schema.tables 
               WHERE table_schema = 'public' AND table_type = 'BASE TABLE' LOOP
        -- 新增created_at字段（若不存在）
        EXECUTE format('ALTER TABLE %I.%I ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP', 'public', tbl.table_name);
        -- 创建触发器（若不存在）
        EXECUTE format('CREATE TRIGGER trigger_set_created_at BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION set_created_at()', 'public', tbl.table_name);
    END LOOP;
END $$;

完成上述操作后，即可使用方案一的SQL进行统计。

注意事项 #

• 若表存在删除/更新操作，上述统计的是新增记录数（即插入的数量），而非当前表中留存的记录数；
• 对于超大规模表，直接实时统计可能影响性能，建议通过定时任务（如cron结合psql）每日/每月生成统计快照，存入专门的报表统计表中；
• 执行SQL的用户需拥有指定模式下所有表的查询、修改权限。

内容的提问来源于stack exchange，提问作者Aspir