Postgresql: run trigger AFTER update FOR EACH STATEMENT ONLY if data changed

Question

In Postgresql I can have two kinds of triggers: FOR EACH ROW and FOR EACH STATEMENT. If I do a FOR EACH ROW trigger, I can add a WHERE clause something like OLD.* != NEW.* so it only fires if something has actually changed. Is there any way to do something similar with STATEMENT level triggers? I know I can't do the same thing since OLD and NEW aren't available, but I was thinking perhaps there might be a way to check the number of rows changed from within my function itself or the like.

Usage case: I am using the postgresql NOTIFY system to notify my app when data changes. Ideally, the app would get a single notification each time one or more records changes, and not get notified at all if data stays the same (even if an UPDATE was run). With a basic AFTER UPDATE FOR EACH STATEMENT trigger, I am getting notified every time an update statement runs - even if it doesn't actually change anything.

klin · Accepted Answer · 2023-01-17 05:48:55Z

You should create two triggers: before update for each row and after update for each statement.

The first trigger checks if the table is being updated and sets a flag if so.

The second trigger checks the flag and performs notify if it was set.

You can use a custom configuration parameter as the flag (e.g. flags.the_table). The solution is simple and safe, as the parameter is local in the current 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();

The function current_setting() with two arguments is available in Postgres 9.6 or later.

Collectives™ on Stack Overflow

Postgresql: run trigger AFTER update FOR EACH STATEMENT ONLY if data changed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related