Postgresql:只有当数据发生变化时才运行trigger AFTER update FOR每条语句

Question

在Postgresql中，我可以有两种类型的触发器:FOR EACH ROW和FOR EACH STATEMENT。如果我做一个FOR EACH ROW触发器，我可以添加一个WHERE子句，比如OLD.* != NEW.*，这样它只在实际发生变化时触发。是否有办法做一些类似的声明级触发器?我知道我不能做同样的事情，因为旧的和新的是不可用的，但我在想也许有一种方法来检查从我的函数本身或类似的改变的行数。

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

Answer 1

您应该创建两个触发器:before update for each row和after update for each statement。

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

第二个触发器检查标志，如果设置了，则执行notify。

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

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或更高版本中可用