如何在PL/pgSQL触发器函数中从变量中派生UPDATE的列名？

Question

如何在PL/pgSQL触发器函数中从变量中派生UPDATE的列名？

4

我有一个在PostgreSQL 9.4中的简单触发器函数：

 BEGIN 
 IF (TG_OP = 'UPDATE') THEN 
 UPDATE relation 
 SET child_name = new.name 
 WHERE table_reference_1 = new.id; 
 END IF; 
 RETURN NULL; 
 END;

是否可以用变量替换table_reference_1（该列名）？我想做这样的事情：

 BEGIN 
 IF (TG_OP = 'UPDATE') THEN 
 UPDATE relation 
 SET child_name = new.name 
 WHERE TG_TABLE_NAME = new.id; 
 END IF; 
 RETURN NULL; 
 END;

"WHERE TG_TABLE_NAME = new.id" 的意思是：
"new.id 等于与父表名称相同的列的值"。

- stkvtflw

一个表名怎么可能和一个id相等呢？但是一般情况下，如果你想要使用存储在变量中的标识符，就需要使用动态SQL：https://stackoverflow.com/search?q=[postgresql]+dynamic+sql - user330315

一个表名怎么可能等于一个id呢？这不是我试图做的事情。请检查描述 - 我添加了更多的解释。 - stkvtflw

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

“纯 SQL 不接受标识符的变量。我看到您的触发器函数有两个选择：”

“1. CASE 表达式”

“对于一些已知的替代方案（以及一个可选的万能选项）。 ”

UPDATE relation r
SET    child_name = NEW.name 
WHERE  CASE TG_TABLE_NAME  -- "switched case"
        WHEN    'possible_column1'          -- value!
          THEN r.possible_column1 = NEW.id  -- identifier!
        WHEN    'possible_column2'
          THEN r.possible_column2 = NEW.id
        --  etc.
        -- ELSE r.default_column = NEW.id
        -- or no ELSE ...
       END;

没有ELSE表示如果没有选项匹配，表达式会计算为NULL。只有TRUE在WHERE子句中才能符合条件。

2. 动态SQL

对于任意数量的替代方案或在编码时未知的替代方案。

EXECUTE format('
   UPDATE relation
   SET    child_name = $1
   WHERE  %I = $2'
 , TG_TABLE_NAME  -- being used as column name
USING NEW.name, NEW.id;

注释

如果列名实际上不存在，则会引发一个异常。除非你捕获它，否则事务会回滚。
PL/pgSQL使用预编译语句。如果Postgres发现重新规划不会生成比通用计划更好的计划，则选项1的查询计划可以在同一会话中被重复使用。选项2每次都会被规划。这可能与您的用例无关/是一个缺点/实际上是一个优势...
- PostgreSQL函数中语言sql和语言plpgsql之间的区别
始终确保动态SQL对SQL注入是安全的（在这种情况下通过恶意构造表名）。我使用%I通过format()进行防御。