Create postgres statement trigger after UPDATE using data from the update to call NOTIFY-CodePudding

I'd like to create a trigger that runs whenever an INSERT statement on a table succeeded, using the data from the insert to call pg_notify:

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $$
   BEGIN
    PERFORM pg_notify(NEW.aggregate_type, NEW.aggregate_id::text);
    RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER model_notification
 AFTER INSERT ON events
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates()

aggregate_type and aggregate_id are columns of the events table. the values for aggregate_type are known in advance and it's possible to call LISTEN on the relevant channels in advance.

The error I get says "channel name cannot be empty". I guess it's because the trigger runs for each statement and not for each row.
Is there a way to make this work for statement triggers? I think the interesting part is that there might be a couple of rows that were inserted and postgres doesn't know which value it should choose?

This question is similar to Using row data in pg_notify trigger as channel name?, but I want to have the trigger run on each statement instead of each row because in my particular case the values for aggregate_type and aggregate_id will be the same for all rows on each insert.

CodePudding user response：

You need a transition table:

CREATE OR REPLACE FUNCTION notify_updates() RETURNS TRIGGER AS $$
   BEGIN
    PERFORM pg_notify(aggregate_type, aggregate_id::text)
      FROM new_table -- transition table
      LIMIT 1;       -- !!
    RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER model_notification
 AFTER INSERT ON events
 REFERENCING NEW TABLE AS new_table -- define transition table name
 FOR EACH STATEMENT
 EXECUTE PROCEDURE notify_updates();

Read more about the feature in the documentation:

Transition tables were introduced in Postgres 10. Read also Trigger that will build one row from several rows.