PostgreSQL存储过程中循环遍历表记录并左连接是否可行？

嘿，我明白你遇到的问题了——你原来的代码出错是因为PostgreSQL的静态SQL不允许直接把变量作为表名嵌入到JOIN语句里，哪怕用了quote_ident()也不行，因为静态SQL在编译阶段就会解析表名，而变量是运行时才确定的。得用动态SQL来处理这种场景，我给你两种实用的解决方案：

方案一：循环执行动态插入 #

这是对你原有代码的改造，用EXECUTE执行动态生成的SQL语句，同时用format()函数安全地注入表名：

CREATE OR REPLACE PROCEDURE fetch_dynamic_table_data()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    dynamic_query TEXT;
BEGIN
    -- 确保临时结果表存在（根据你的实际字段类型调整）
    CREATE TEMP TABLE IF NOT EXISTS temp_Results (
        Key INT,
        pk_timestamp TIMESTAMP
    );

    -- 遍历存储表名的记录
    FOR r IN SELECT tablename FROM tablewithtablenames ORDER BY tablename ASC LOOP
        -- 用format()和%I占位符安全构造SQL，%I会自动转义表名防止注入
        dynamic_query := format(
            'INSERT INTO temp_Results 
             SELECT temp_ids.Key as Key, loggedvalue.pk_timestamp 
             FROM temp_ids 
             LEFT JOIN %I AS loggedvalue ON temp_ids.Key = loggedvalue.pk_fk_id',
            r.tablename
        );
        
        -- 执行动态生成的SQL
        EXECUTE dynamic_query;
    END LOOP;
END;
$$;

关键说明： #

• format()函数的%I占位符会自动调用quote_ident()，确保表名包含特殊字符或关键字时也能正确解析，同时避免SQL注入风险。
• 必须用EXECUTE来运行动态SQL，因为静态SQL无法处理运行时才确定的表名。

方案二：一次性生成UNION ALL查询（更高效） #

如果你的表数量较多，循环执行多次插入会有性能开销，不如直接生成一个包含所有表的UNION ALL查询，一次性插入结果：

CREATE OR REPLACE PROCEDURE fetch_dynamic_table_data_bulk()
LANGUAGE plpgsql
AS $$
DECLARE
    dynamic_query TEXT;
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS temp_Results (
        Key INT,
        pk_timestamp TIMESTAMP
    );

    -- 生成所有表的查询语句，用UNION ALL拼接
    SELECT string_agg(
        format(
            'SELECT temp_ids.Key as Key, %I.pk_timestamp 
             FROM temp_ids 
             LEFT JOIN %I ON temp_ids.Key = %I.pk_fk_id',
            r.tablename, r.tablename, r.tablename
        ),
        ' UNION ALL '
    ) INTO dynamic_query
    FROM (SELECT tablename FROM tablewithtablenames ORDER BY tablename ASC) r;

    -- 执行批量插入
    EXECUTE format('INSERT INTO temp_Results %s', dynamic_query);
END;
$$;

额外注意事项： #

• 确保所有通过tablewithtablenames获取的表都存在pk_fk_id和pk_timestamp字段，否则执行时会抛出字段不存在的错误。
• 根据你的实际数据类型调整temp_Results表的字段类型，比如Key如果是UUID就改成UUID类型。
• 如果临时表temp_ids是会话级别的，要确保在调用存储过程前已经创建并填充了数据。

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