如何用PL/PgSQL自动更新PostgreSQL中含每日表的联合视图？

这绝对是PostgreSQL里能搞定的需求，我平时处理这类按日分表的场景时，经常用PL/PgSQL结合定时任务来自动维护联合视图。下面给你一步步拆解实现方案：

第一步：编写PL/PgSQL函数自动更新视图 #

首先要写一个函数，核心逻辑是找到前一天的分表，检查它是否存在且未被加入视图，然后更新联合视图。假设你的分表命名规则是类似daily_data_YYYYMMDD（比如daily_data_20240520），视图名为all_daily_data，函数可以这么写：

CREATE OR REPLACE FUNCTION refresh_daily_union_view()
RETURNS void AS $$
DECLARE
    yesterday_table text;
    current_view_def text;
BEGIN
    -- 生成前一天的表名（按你的命名规则调整）
    yesterday_table := 'daily_data_' || to_char(current_date - 1, 'YYYYMMDD');
    
    -- 检查前一天的表是否存在
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = yesterday_table) THEN
        RAISE NOTICE '表 % 不存在，跳过更新', yesterday_table;
        RETURN;
    END IF;
    
    -- 获取当前视图的定义
    SELECT definition INTO current_view_def FROM pg_views WHERE viewname = 'all_daily_data';
    
    -- 检查视图是否已经包含该表（避免重复添加）
    IF current_view_def LIKE '%' || yesterday_table || '%' THEN
        RAISE NOTICE '表 % 已在视图中，跳过更新', yesterday_table;
        RETURN;
    END IF;
    
    -- 动态生成新的视图定义并替换原视图
    EXECUTE 'CREATE OR REPLACE VIEW all_daily_data AS ' 
            || current_view_def || ' UNION ALL SELECT * FROM ' || quote_ident(yesterday_table);
    
    RAISE NOTICE '成功将表 % 加入视图 all_daily_data', yesterday_table;
END;
$$ LANGUAGE plpgsql;

函数里的关键细节： #

• 用quote_ident()处理表名，避免SQL注入风险
• 先检查表是否存在、是否已在视图中，防止无效操作或报错
• 用RAISE NOTICE记录操作日志，方便排查问题

第二步：设置定时任务自动执行函数 #

PostgreSQL本身没有内置的定时任务，但可以通过官方扩展pg_cron来实现，这是最常用的方案：

1. 首先安装pg_cron扩展（如果还没装）：

CREATE EXTENSION IF NOT EXISTS pg_cron;

2. 然后创建每日执行的定时任务，比如设置每天凌晨1点执行更新函数：

-- 每天凌晨1点运行refresh_daily_union_view函数
SELECT cron.schedule(
    'daily-refresh-union-view',
    '0 1 * * *',
    'SELECT refresh_daily_union_view();'
);

定时任务的注意事项： #

• 确保pg_cron的服务已经启动（不同操作系统的启动方式略有差异，比如Linux下可能需要重启PostgreSQL服务）
• 执行定时任务的用户需要有足够的权限（比如调用函数、访问表的权限）

其他需要考虑的点 #

• 命名规则一致性：必须保证每日生成的分表严格遵循固定命名格式，否则函数无法正确识别
• 权限配置：运行函数的用户需要SELECT权限所有分表，以及CREATE VIEW权限
• 异常处理：如果需要更健壮的逻辑，可以在函数里添加EXCEPTION块捕获错误，避免定时任务中断
• 视图性能：如果分表数量很多，联合视图的查询性能可能下降，这时可以考虑用分区表替代分表+联合视图的方案（分区表是PostgreSQL官方推荐的分表方案）

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