PostgreSQL遍历Worklist表创建物化视图的问题求助

问题核心分析 #

当前代码存在三个关键问题：

1. 变量名歧义：声明的tablename变量与pg_tables系统表的tablename列重名，导致PostgreSQL无法区分引用对象，触发column reference "tablename" is ambiguous错误。
2. 静态SQL不支持变量表名：循环中直接用FROM tablename属于静态SQL语法，PL/pgSQL不允许将变量作为表名直接使用，必须通过动态SQL实现。
3. 日期逻辑错误：原查询中(SELECT doe - INTERVAL '30 days')的子查询会返回整个表的doe值集合，逻辑上需使用基准日期（如当前日期）计算30天区间，否则统计结果完全错误。

修正后的完整解决方案 #

1. 先创建物化视图结构 #

定义包含表名标识和所有统计字段的物化视图初始结构：

CREATE MATERIALIZED VIEW worklist_stats AS
SELECT 
    '' AS table_name,
    0 AS total_claim_count,
    0 AS assigned_count,
    0 AS not_assigned_count,
    0 AS assigned_last_30_days,
    0 AS not_assigned_last_30_days,
    0 AS assigned_before_30_days,
    0 AS not_assigned_before_30_days,
    0 AS followed_up,
    0 AS denials,
    0 AS no_status
WHERE 1 = 0; -- 初始化空表

2. 编写PL/pgSQL脚本遍历统计所有Worklist表 #

使用动态SQL执行统计，同时修正变量名和日期逻辑：

DO $$
DECLARE
    tbl_name record;
    v_tablename varchar; -- 重命名变量避免与列名冲突
    v_sql text;
BEGIN
    -- 清空物化视图原有数据
    TRUNCATE TABLE worklist_stats;

    -- 遍历public schema下所有worklist_前缀的表
    FOR tbl_name IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public' 
          AND tablename LIKE 'worklist\_%' ESCAPE '\' -- 修正转义符为PostgreSQL标准的\
    LOOP
        v_tablename := tbl_name.tablename;

        -- 构造动态SQL，以当前日期为基准计算30天区间
        v_sql := format(
            'INSERT INTO worklist_stats (
                table_name,
                total_claim_count,
                assigned_count,
                not_assigned_count,
                assigned_last_30_days,
                not_assigned_last_30_days,
                assigned_before_30_days,
                not_assigned_before_30_days,
                followed_up,
                denials,
                no_status
            )
            SELECT 
                %L AS table_name,
                count(visit_num) AS total_claim_count,
                count(user_id) AS assigned_count,
                (count(visit_num) - count(user_id)) AS not_assigned_count,
                count(case when doe <= CURRENT_DATE - INTERVAL ''30 days'' AND user_id IS NOT NULL then 1 end) AS assigned_last_30_days,
                (count(user_id) - count(case when doe <= CURRENT_DATE - INTERVAL ''30 days'' AND user_id IS NOT NULL then 1 end)) AS not_assigned_last_30_days,
                count(case when doe > CURRENT_DATE - INTERVAL ''30 days'' AND user_id IS NOT NULL then 1 end) AS assigned_before_30_days, -- 修正条件避免重复统计边界日期
                count(case when doe > CURRENT_DATE - INTERVAL ''30 days'' AND user_id IS NULL then 1 end) AS not_assigned_before_30_days,
                count(CASE WHEN date_worked IS NOT NULL then 1 end) AS followed_up,
                count(CASE WHEN last_denied_date IS NOT NULL then 1 end) AS denials,
                count(case when status_and_action_code = ''No Status - Need to follow-up'' then 1 end) AS no_status
            FROM %I;',
            v_tablename,
            v_tablename
        );

        -- 执行动态SQL
        EXECUTE v_sql;
    END LOOP;

    -- 刷新物化视图确保数据可见
    REFRESH MATERIALIZED VIEW worklist_stats;
END; $$;

关键修正说明 #

• 变量名重命名：将tablename改为v_tablename，彻底解决列名与变量名的歧义问题。
• 动态SQL实现：使用format()函数构造安全的动态查询，%L处理字符串常量（表名标识），%I处理标识符（表名），避免SQL注入风险。
• 日期逻辑修正：用CURRENT_DATE - INTERVAL '30 days'作为基准日期，同时调整区间条件避免重复统计边界日期的数据。
• 物化视图数据填充：先初始化空结构，再通过循环插入每个表的统计结果，最终得到所有Worklist表的汇总统计。

内容的提问来源于stack exchange，提问作者K.D.Gayan