如何在PostgreSQL触发器中用NEW实现动态列名的大写转换？

你的问题出在动态SQL的使用方式上，触发器函数里的NEW行变量不能直接在动态SQL字符串中赋值，也不能错误地将触发器参数TG_ARGV[0]当作NEW的属性来访问。以下是正确的实现方案：

正确的触发器函数写法 #

CREATE OR REPLACE FUNCTION uppercase()
RETURNS TRIGGER AS $$
BEGIN
  -- 动态获取指定列的大写值，并赋值给NEW的对应列
  EXECUTE FORMAT('SELECT UPPER($1.%I)', TG_ARGV[0]) 
  INTO NEW.%I;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器示例 #

假设你要对users表的email列在插入/更新前转大写，触发器创建语句如下：

CREATE TRIGGER trigger_users_email_uppercase
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION uppercase('email');

错误原因说明 #

• 第一种写法：EXECUTE '$1.TG_ARGV[0] := UPPER($1.TG_ARGV[0])' USING NEW
  这里的TG_ARGV[0]是触发器的参数，并非NEW行变量的属性，不能通过$1.TG_ARGV[0]来引用列，语法逻辑完全错误。
• 第二种写法：EXECUTE FORMAT('NEW.%I := UPPER(NEW.%I)', TG_ARGV[0], TG_ARGV[0])
  动态SQL的执行上下文无法直接访问触发器函数中的NEW变量，而且这种直接赋值的方式不符合PL/pgSQL动态SQL的语法规范，必须通过INTO子句将动态查询的结果赋值给NEW的列。

内容的提问来源于stack exchange，提问作者jean-yves boisiaud