sql - 如何从 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

普通 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;

注意事项

• 如果列名实际上不存在，则会引发异常。您的交易已回滚 unless you trap it .

• PL/pgSQL 使用准备好的语句进行操作。如果 Postgres 发现重新规划不会生成比通用计划更好的计划，则选项 1 的查询计划可以在同一 session 中重复使用。 每次都计划选项 2。这可能无关紧要/不利/实际优势，具体取决于您的用例......

  • Difference between language sql and language plpgsql in PostgreSQL functions

• 始终确保动态 SQL 可以安全地防止 SQL 注入(inject)(在本例中是通过恶意制作的表名)。我用 format() 防御它使用 %I。

更多解释的相关答案:

• Table name as a PostgreSQL function parameter
• INSERT with dynamic table name in trigger function
• Update multiple columns in a trigger function in plpgsql
• Difference between language sql and language plpgsql in PostgreSQL functions