如何向Postgres函数内的动态SELECT查询传入数组输入参数

• 你直接将复合数组类型的输入参数i_emp_name和动态SQL字符串拼接，PostgreSQL无法将复合类型数组隐式转换为符合语法规则的SQL字面量，拼接后的SQL存在格式错误，因此抛出malformed array literal报错
• 原代码还有两处笔误：DECLARE块遗漏了V_EMP_QRY变量的声明，查询字段别名和表别名不匹配，都会额外触发执行报错

使用EXECUTE ... USING语法传递参数，完全避免参数和SQL字符串直接拼接，既解决格式问题，还能避免SQL注入风险，修改后的完整函数代码如下：

CREATE TYPE schema.emp_name_type as (
emp_name varchar);-- 单字段复合类型，存储员工姓名

CREATE OR REPLACE FUNCTION schema.emp_data2(
    i_emp_name schema.emp_name_type[])-- 数组类型输入参数
    RETURNS TABLE(emp_name character varying, emp_address character varying) -- 返回姓名和地址
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 5000
AS $BODY$
DECLARE
   V_ORDER_BY text;
   V_MAIN_QRY text;
   V_EMP_QRY text; -- 补全遗漏的变量声明
BEGIN

   V_EMP_QRY:=
            $$ SELECT
e.emp_name,e.emp_address
 FROM schema.emp e
WHERE 1=1
AND e.emp_name in (select a.emp_name from unnest($1) a)$$; -- 用$1作为参数占位符，不直接拼接参数

V_ORDER_BY := ' ORDER BY 1 ';

V_MAIN_QRY := V_EMP_QRY || V_ORDER_BY;

RETURN QUERY EXECUTE V_MAIN_QRY USING i_emp_name; -- 通过USING子句传入数组参数

RAISE NOTICE '%',V_MAIN_QRY;
END;
$BODY$;

函数的调用方式无需修改，直接用原有调用语句即可：

select * from schema.emp_data2
(array[row('John'),
row('Roy'),
row('James')]::schema.emp_name_type[]);

如果确实有需要把参数直接拼接到SQL字符串中，可以用format函数自动转义数组字面量，示例如下：

V_EMP_QRY:= format(
            $$ SELECT
e.emp_name,e.emp_address
 FROM schema.emp e
WHERE 1=1
AND e.emp_name in (select a.emp_name from unnest(%L) a)$$, i_emp_name);

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