PostgreSQL 14：PL/pgSQL中为PREPARE传动态列名的正确语法

PostgreSQL预处理语句的参数仅支持值替换，无法直接用来替换列名、表名这类标识符。你当前的代码把列名作为文本参数传入预处理语句，导致WHERE子句变成了'my_field1' = 0这种形式——这是在将字符串与数字0做比较，必然会触发类型不匹配（“运算符不存在”）或逻辑错误。

要动态替换列名这类标识符，必须在定义预处理语句阶段就用FORMAT函数生成包含正确列名的SQL，而非把列名作为参数传递给已定义好的预处理语句。结合你的需求，正确写法如下：

DO
$$
DECLARE
    rtmp1 record;   
    rtmp2 record;   
    rtmp3 record;   
    col_name1 text := 'my_field1';
    col_name2 text := 'my_field2';
    col_name3 text := 'my_field3';
BEGIN
    DEALLOCATE ALL;

    -- 处理第一个列：动态生成预处理语句
    EXECUTE FORMAT('PREPARE q_test AS
        SELECT first_name
        FROM my_table
        WHERE %I = 0', col_name1);
    EXECUTE 'EXECUTE q_test' INTO rtmp1;

    -- 重新定义预处理语句处理第二个列
    EXECUTE FORMAT('PREPARE q_test AS
        SELECT first_name
        FROM my_table
        WHERE %I = 0', col_name2);
    EXECUTE 'EXECUTE q_test' INTO rtmp2;

    -- 处理第三个列
    EXECUTE FORMAT('PREPARE q_test AS
        SELECT first_name
        FROM my_table
        WHERE %I = 0', col_name3);
    EXECUTE 'EXECUTE q_test' INTO rtmp3;

    -- 若需传递动态值参数（比如把0换成变量），可参考以下写法：
    -- DECLARE filter_val integer := 0;
    -- EXECUTE FORMAT('PREPARE q_test(integer) AS
    --     SELECT first_name
    --     FROM my_table
    --     WHERE %I = $1', col_name1);
    -- EXECUTE 'EXECUTE q_test($1)' USING filter_val INTO rtmp1;
END
$$;

核心注意事项 #

• 用FORMAT函数的%I占位符处理标识符，它会自动完成标识符转义，避免SQL注入和语法错误。
• 预处理语句的参数仅用于传递数值/字符串等值类数据，不能用于标识符替换。
• 如果你的查询逻辑仅列名变化，其余固定，也可以跳过预处理语句，直接用EXECUTE ... USING实现，代码更简洁且性能相当：

DO
$$
DECLARE
    rtmp1 record;   
    rtmp2 record;   
    rtmp3 record;   
    col_name1 text := 'my_field1';
    col_name2 text := 'my_field2';
    col_name3 text := 'my_field3';
    filter_val integer := 0;
BEGIN
    EXECUTE FORMAT('SELECT first_name FROM my_table WHERE %I = $1', col_name1)
        INTO rtmp1 USING filter_val;
    EXECUTE FORMAT('SELECT first_name FROM my_table WHERE %I = $1', col_name2)
        INTO rtmp2 USING filter_val;
    EXECUTE FORMAT('SELECT first_name FROM my_table WHERE %I = $1', col_name3)
        INTO rtmp3 USING filter_val;
END
$$;

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