PostgreSQL PL/pgSQL函数中动态Merge传递复合记录数组问题

方法1：直接通过USING传递数组并在动态SQL中unnest #

这是最简洁且安全的方案，无需临时表，直接将复合记录数组作为参数传入动态SQL，避免并发冲突和SQL注入风险：

CREATE OR REPLACE FUNCTION dbo.bulk_merge(
    TableName text,
    user_bulk_data dbo.compositerecordtype[]
) RETURNS void AS $$
DECLARE
    dynamic_sqlStatement text;
BEGIN
    -- 用format函数安全处理表名转义，防止SQL注入
    dynamic_sqlStatement := format(
        $$
            MERGE INTO %I AS TRG
            USING (
                SELECT cola, colb, colc, id 
                FROM unnest($1) AS SRC
            ) AS SRC
            ON (TRG.id = SRC.id)
            WHEN NOT MATCHED THEN
                INSERT (cola, colb, colc) 
                VALUES (SRC.cola, SRC.colb, SRC.colc)
            WHEN MATCHED THEN
                UPDATE SET 
                    colb = SRC.colb, 
                    colc = SRC.colc
        $$,
        TableName
    );

    -- 执行动态SQL，通过USING传递数组参数
    EXECUTE dynamic_sqlStatement USING user_bulk_data;
END;
$$ LANGUAGE plpgsql;

关键说明：

• 使用format函数的%I占位符自动转义表名，避免SQL注入。
• 动态SQL中用$1引用通过USING传入的数组参数，unnest($1)直接展开复合记录数组作为数据源。
• 无需临时表，彻底解决并发冲突问题，性能更优。

方法2：正确使用临时表（若必须依赖临时表场景） #

如果业务逻辑需要临时表中转，需确保动态SQL能正确识别临时表（临时表默认属于pg_temp模式），同时避免多次调用的数据残留：

CREATE OR REPLACE FUNCTION dbo.bulk_merge_with_temp(
    TableName text,
    user_bulk_data dbo.compositerecordtype[]
) RETURNS void AS $$
DECLARE
    dynamic_sqlStatement text;
BEGIN
    -- 创建临时表（若不存在），并清空历史数据
    CREATE TEMP TABLE IF NOT EXISTS bulk_data_table OF dbo."compositerecordtype";
    TRUNCATE TABLE bulk_data_table;

    -- 将数组数据插入临时表
    INSERT INTO bulk_data_table 
    SELECT (rec).* FROM UNNEST(user_bulk_data) AS rec;

    -- 构建动态SQL，明确引用pg_temp模式下的临时表
    dynamic_sqlStatement := format(
        $$
            MERGE INTO %I AS TRG
            USING (
                SELECT cola, colb, colc, id 
                FROM pg_temp.bulk_data_table
            ) AS SRC
            ON (TRG.id = SRC.id)
            WHEN NOT MATCHED THEN
                INSERT (cola, colb, colc) 
                VALUES (SRC.cola, SRC.colb, SRC.colc)
            WHEN MATCHED THEN
                UPDATE SET 
                    colb = SRC.colb, 
                    colc = SRC.colc
        $$,
        TableName
    );

    EXECUTE dynamic_sqlStatement;
END;
$$ LANGUAGE plpgsql;

关键修复点：

• 用pg_temp.bulk_data_table明确指定临时表的模式，确保动态SQL能正确访问（之前的错误是直接拼接表对象而非表名字符串）。
• 每次调用前用TRUNCATE清空临时表，避免多次执行时的数据污染。

原代码失败原因 #

1. 表名拼接错误：|| bulk_data_table ||是将表对象转换为字符串（而非表名字符串），导致动态SQL中引用了无效的标识符。
2. 临时表作用域误解：PostgreSQL临时表在整个会话内可见，同一会话的EXECUTE语句完全可以访问，问题出在表名的错误引用而非作用域。

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