从通用触发器中的不同列中获取值 [英] Get values from varying columns in a generic trigger

问题描述

我是PostgreSQL的新手，他发现了一个触发器，除了一件小事情外，它完全可以达到我的目的.该触发器非常通用，可跨不同的表运行并记录不同的字段更改.我在此处找到了

I am new to PostgreSQL and found a trigger which serves my purpose completely except for one little thing. The trigger is quite generic and runs across different tables and logs different field changes. I found here.

我现在需要做的是测试一个特定的字段，该字段会随着触发触发器的表的更改而改变.我想到使用substr，因为所有列将具有相同的名称格式，例如XXX_cust_no，但是XXX可以更改为2个或4个字符.我需要将每个记录写入history_/审计表的记录记录在XXX_cust_no字段中.我不愿意使用一堆IF/ELSE语句来完成此操作.

What I now need to do is test for a specific field which changes as the tables change on which the trigger fires. I thought of using substr as all the column will have the same name format e.g. XXX_cust_no but the XXX can change to 2 or 4 characters. I need to log the value in theXXX_cust_no field with every record that is written to the history_ / audit table. Using a bunch of IF / ELSE statements to accomplish this is not something I would like to do.

现在可以使用的触发器记录了table_name，column_name，old_value，new_value.但是，我还需要记录已更改记录的XXX_cust_no.

The trigger as it now works logs the table_name, column_name, old_value, new_value. I however need to log the XXX_cust_no of the record that was changed as well.

推荐答案

基本上，对于动态列名，您需要使用动态SQL. format帮助格式化DML命令.通过USING子句传递NEW和OLD中的值.

Basically you need dynamic SQL for dynamic column names. format helps to format the DML command. Pass values from NEW and OLD with the USING clause.

给出这些表:

CREATE TABLE tbl (
  t_id serial PRIMARY KEY
 ,abc_cust_no text
);

CREATE TABLE log (
  id          int
 ,table_name  text
 ,column_name text
 ,old_value   text
 ,new_value   text
);

它可以像这样工作:

CREATE OR REPLACE FUNCTION trg_demo()
  RETURNS TRIGGER AS
$func$
BEGIN

EXECUTE format('
   INSERT INTO log(id, table_name, column_name, old_value, new_value)
   SELECT ($2).t_id
         , $3
         , $4
         ,($1).%1$I
         ,($2).%1$I', TG_ARGV[0])
USING OLD, NEW, TG_RELNAME, TG_ARGV[0];

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER demo
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('abc_cust_no'); -- col name here.

SQL小提琴.

关于dba.SE的相关答案:

Related answer on dba.SE:

• 如何仅给出字段名称来访问新"或旧"字段?

手册中plpgsql触发函数中可见的特殊变量列表.

这篇关于从通用触发器中的不同列中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！