主页 / dba / 问题 / 217781
Accepted
exhuma
Asked: 2018-09-18 04:21:36 +0800 CST

如何编写一个以表名作为参数的“on-update”触发器？

  • 772

注意：以下示例是一个非常简化的代码，但它在一个可重现的示例中说明了任务。看着这个例子，你可能会想“为什么要这样做”？实际上，完整的任务是为多个表存储审计跟踪，但前提是满足特定条件。每个表的条件都相同（它们各自共享一些列，如inserted，updated等等）。因此，每个表何时存储审计跟踪的代码都是相同的。但是每次要复制的实际列都不同。我想创建一个触发器来动态处理这个问题，这样我就不需要在每次架构更改时都触摸它。

考虑以下工作示例（下面的问题）。这演示了一个简单的模式，其中表中的每个更新data都会导致将旧值移入data2：

DROP TABLE IF EXISTS data CASCADE;
DROP TABLE IF EXISTS data2 CASCADE;
CREATE TABLE data (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);
CREATE TABLE data2 (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated = NOW();
        INSERT INTO data2 VALUES (OLD.*);
        RETURN NEW;
    END;
    $$ language 'plpgsql';

CREATE TRIGGER update_trigger
    BEFORE UPDATE ON data
    FOR EACH ROW
    EXECUTE PROCEDURE update_trigger_func();


SET client_min_messages TO 'debug';
INSERT INTO data (name) VALUES ('foo');
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'bar';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'baz';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

请注意，该函数update_trigger_func具有硬编码的“history”表名data2，如下行所示：

INSERT INTO data2 VALUES (OLD.*);

如果data2是一个参数，这个函数也可以重用于其他表。但到目前为止我没能找到合适的咒语。到目前为止，我已经尝试了以下两个版本：

INSERT INTO TG_ARGV[0] VALUES (OLD.*);

但这会导致语法错误：

psql:temptable.sql:28: ERROR:  syntax error at or near "VALUES"
LINE 11:         INSERT INTO TG_ARGV[0] VALUES (OLD.*);

因此，或者我尝试使用动态 SQL：

sql := 'INSERT INTO' || TG_ARGV[0] || 'VALUES (OLD.*)';
EXECUTE sql;

但这失败了，因为该OLD变量在执行上下文中不可用：

psql:temptable.sql:58: ERROR:  missing FROM-clause entry for table "old"
LINE 1: INSERT INTO data2 VALUES (OLD.*)
                                    ^
QUERY:  INSERT INTO data2 VALUES (OLD.*)
CONTEXT:  PL/pgSQL function versioned_update() line 11 at EXECUTE

鉴于我想在其他表上使用此触发函数，我无法对列名进行硬编码。我怎么能做到这一点？

postgresql trigger

1 个回答

  1. Best Answer

    不幸的是，由于 的数据类型OLD是data，谷歌搜索相当困难，并且在谷歌中搜索它没有找到任何可用的东西。此外，找出它data是复合类型是找到解决方案的关键。

    问题是双重的：

    • 该表不能直接在INSERT语句中使用，因为它是类型text。所以必须使用动态SQL。所以不是直接写

      INSERT INTO TG_ARGV[0] VALUES OLD;

      必须使用以下内容：

      sql := format(INSERT INTO %I VALUES ...', TG_ARGV[0]);

    • 该OLD变量不能直接在EXECUTE语句中使用。因此，从之前的要点构建，这是不可能的：

       sql := format(INSERT INTO %I VALUES OLD', TG_ARGV[0]);

      而必须使用关键字OLD传递来自的值：USING

       sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
 EXECUTE sql USING OLD;

    最终的解决方案：

    CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    DECLARE
        sql TEXT;
    BEGIN
        NEW.updated = NOW();
        sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
        EXECUTE sql USING OLD;
        RETURN NEW;
    END;
    $$ language 'plpgsql';

    这允许我们编写一个触发器来处理data表上的事件并将历史记录写入history表中：

     CREATE TRIGGER data_versioning_trigger_delete
     BEFORE DELETE ON data
     FOR EACH ROW EXECUTE PROCEDURE update_trigger_func('history');
    • 2

相关问题