如何在PostgreSQL函数中动态带参执行预准备语句？

问题场景 #

你已完成以下操作：

1. 创建并初始化person表：

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

2. 创建预准备语句my_pre用于更新指定id的age：

PREPARE my_pre(INT, INT) AS
  UPDATE person SET age = $1 WHERE id = $2;

3. 编写PL/pgSQL函数my_func尝试调用该预准备语句，但调用时出现ERROR: there is no parameter $1错误：

CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre($1, $2)' USING age, id;
END;
$$ LANGUAGE plpgsql;

错误原因 #

你错误地将EXECUTE my_pre($1, $2)作为动态SQL字符串执行，导致参数传递逻辑混淆：

• 内层EXECUTE my_pre($1, $2)中的$1、$2被PostgreSQL识别为预准备语句my_pre的参数，但执行该动态SQL时并未为这些参数提供值；
• 外层的USING age, id是为动态SQL本身传递参数，但动态SQL字符串中并没有定义属于它的占位符，因此出现参数不存在的错误。

解决方案 #

方案1：直接调用预准备语句（推荐）

PL/pgSQL的EXECUTE命令支持直接调用预准备语句，无需嵌套EXECUTE字符串，直接用USING传递参数即可：

CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE my_pre USING age, id;
END;
$$ LANGUAGE plpgsql;

方案2：动态SQL方式（适用于预准备语句名动态生成的场景）

如果你的预准备语句名是动态的（比如需要根据输入参数变化），可以用format函数拼接动态SQL，同时通过USING安全传递参数：

CREATE FUNCTION my_func(age INT, id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE format('EXECUTE %I($1, $2)', 'my_pre') USING age, id;
END;
$$ LANGUAGE plpgsql;

这里%I用于安全处理标识符（避免SQL注入），动态SQL中的$1、$2对应USING传递的参数，执行时会被替换为实际值后调用预准备语句。

验证 #

调用函数后检查数据：

SELECT my_func(45, 2);
SELECT * FROM person WHERE id = 2;

会返回David的age已更新为45。

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito