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

Question

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.

klin · Accepted Answer · 2022-04-23 12:20:11Z

1

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.

edited Apr 23, 2022 at 12:20

answered Apr 22, 2022 at 23:59

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

l7r7 Over a year ago

I completely missed the first link to the docs, that was helpful. I also had to do another cast for aggregate_type to text (it's a varchar column), which in turn requires me to add quotes in the LISTEN command. That's ok for now as a workaround and beyond the scope of this question here

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related