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

Question

我在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等于列的值，列的名称等于父表的名称"。

Answer 1

Plain 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;

No 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注入是安全的（在这种情况下，通过恶意编制的表名）。我用format()和%I来防御它。

相关答案及更多解释：

• 表名作为PostgreSQL函数参数
• 在触发器函数中使用动态表名的INSERT
• 在plpgsql中更新触发器函数中的多列
• PostgreSQL函数中sql语言与plpgsql语言的区别