Postgresql:仅当数据更改时，才在更新每个语句后运行触发器

Question

在 Postgresql 中我可以有两种触发器:FOR EACH ROW 和 FOR EACH STATEMENT。如果我执行 FOR EACH ROW 触发器，我可以添加一个 WHERE 子句，例如 OLD.* != NEW.* 这样它只会在实际发生更改时触发。有什么方法可以用 STATEMENT 级别的触发器做类似的事情吗？我知道我不能做同样的事情，因为 OLD 和 NEW 不可用，但我在想也许有一种方法可以检查从我的函数本身或类似函数中更改的行数。

用例:我正在使用 postgresql NOTIFY 系统在数据更改时通知我的应用程序。理想情况下，每次一个或多个记录更改时，应用程序都会收到一个通知，如果数据保持不变(即使运行了 UPDATE)，则根本不会收到通知。使用基本的 AFTER UPDATE FOR EACH STATEMENT 触发器，每次更新语句运行时我都会收到通知 - 即使它实际上没有改变任何东西。

Answer 1

您应该创建两个触发器:每行更新前和每条语句更新后。

第一个触发器检查表是否正在更新，如果是则设置一个标志。

第二个触发器检查标志并在设置时执行通知。

您可以使用自定义配置参数作为标志(例如 flags.the_table)。该解决方案简单且安全，因为参数在当前 session 中是本地的。

create or replace function before_each_row_on_the_table()
returns trigger language plpgsql
as $$
begin
    if new <> old then
        set flags.the_table to 'on';
    end if;
    return new;
end $$;

create or replace function after_each_statement_on_the_table()
returns trigger language plpgsql
as $$
begin
    if current_setting('flags.the_table', true) = 'on' then
        notify your_channel, 'the_table was updated';
        set flags.the_table to 'off';
    end if;
    return null;
end $$;

create trigger before_each_row_on_the_table
before update on the_table
for each row execute procedure before_each_row_on_the_table();

create trigger after_each_statement_on_the_table
after update on the_table
for each statement execute procedure after_each_statement_on_the_table();

带有两个参数的函数 current_setting() 在 Postgres 9.6 或更高版本中可用。