时间戳

首页 > 解决方案 > 使用旧的触发器动态获取列名

postgresql - 使用旧的触发器动态获取列名

问题描述

我想写一个通用的触发函数（Postgres 过程）。有许多主表，如TableA、TableB等，以及它们对应的审计表TableA_Audit、TableB_Audit。结构如下。

TableA 和 TableA_Audit 具有列 aa integer、ab integer。

TableB 和 TableB_Audit 的列是整数。

同样，可以有许多主表及其审计表。

要求是任何主表都得到更新，然后它们的条目应插入各自的审计表中。

例如：- 如果 TableA 有这样的条目

                ---------------------
                |     **TableA**   |
                ---------------------
                |aa      | ab       |
                |--------|----------|
                |    5   |  10      |
                ---------------------

然后我写了一个更新，比如

更新 TableA 设置 aa= aa+15，

那么 TableA 的旧值应该插入到 TableA_Audit 表中，如下所示

TableA_audit 包含：-

                ---------------------
                |  **TableA_Audit** |
                ---------------------
                |aa      | ab       |
                |--------|----------|
                |    5   |  10      |
                ---------------------

为了简化上述场景，我编写了一个名为 insert_in_audit 的通用函数。每当主表中有任何更新时，应调用函数 insert_in_audit。该功能应达到以下要求：-

  1. 使用主表在相应的 audit_table 中动态插入条目。如果表 B 中有更新，则应仅在 TableB_Audit 中插入条目。

到现在为止我能做到的。我得到了发生更新的主表的所有列的名称。例如：对于查询 - 更新 TableA 设置 aa=aa+15，我能够在 varchar 数组中获取 TableA 中的所有列名。

column_names varchar[ ] := '{"aa", "ab"}';

我的问题是如何获取 aa 和 ab 列的旧值。我试着这样做

foreach i in array  column_names
loop
 raise notice '%', old.i;
end loop;

但是上面给了我错误：-记录“旧”没有字段“i”。任何人都可以帮助我获得旧值。

标签： postgresqlplpgsqlprocedure

解决方案

这是一个代码示例，您可以如何从OLDPL/pgSQL 中动态提取值：

CREATE FUNCTION dynamic_col() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   v_col name;
   v_val text;
BEGIN
   FOREACH v_col IN ARRAY TG_ARGV
   LOOP
      EXECUTE format('SELECT (($1).%I)::text', v_col)
         USING OLD
         INTO v_val;
      RAISE NOTICE 'OLD.% = %', v_col, v_val;
   END LOOP;

   RETURN OLD;
END;$$;

CREATE TABLE trigtest (
   id integer,
   val text
);

INSERT INTO trigtest VALUES
   (1, 'one'), (2, 'two');

CREATE TRIGGER dynamic_col AFTER DELETE ON trigtest
   FOR EACH ROW EXECUTE FUNCTION dynamic_col('id', 'val');

DELETE FROM trigtest WHERE id = 1;

NOTICE:  OLD.id = 1
NOTICE:  OLD.val = one

推荐阅读