Using row data in pg_notify trigger as channel name?

Question

Is it possible to use data from the row a trigger is firing on, as the channel of a pg_notify, like this:

CREATE OR REPLACE FUNCTION notify_pricesinserted()
  RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify(
    NEW.my_label,
    row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_pricesinserted
  AFTER INSERT ON prices
  FOR EACH ROW
  EXECUTE PROCEDURE notify_pricesinserted();

EDIT: I found out the reason it was not working is due to the case of my label. If I replace it with lower(NEW.my_label) and also do the same for the listener then it works.

It does not work as far as I can tell. I also tried this: CAST(NEW.symbol AS text) , but still no difference. PostgreSQL does not throw any error, but in Node I don't get notified: client.query("LISTEN btceUSD",function(err, result); I also tried lowecarse format but it didn't make a difference. — Stefan D
– Stefan D, Commented Aug 2, 2015 at 15:28
"LISTEN btceUSD" should be "LISTEN \"btceUSD\"" names in postgres are folded to lower-case unless quoted. — Jasen
– Jasen, Commented Aug 3, 2015 at 0:18
Note that you have to issue a LISTEN btceUSD statement to set up the notification, but you then have to regularly poll the server for new messages. For an example in Java, see the JDBC help page here. — Patrick
– Patrick, Commented Aug 3, 2015 at 4:30

Patrick · Accepted Answer · 2015-08-02 11:53:19Z

1

The pg_notify() part would work without throwing an error. PostgreSQL places very few restrictions on what a channel name could be. But in practice it is probably useless because you would need to establish a LISTEN some_channel command prior to the pg_notify() statement to pick up the payload message somewhere outside of the trigger function and doing that on some dynamic value is difficult in most situations and probably terribly inefficient in all cases.

If - in your trigger - NEW.my_label has a small number of well-defined values, then you might work it out by establishing listening channels on all possible values, but you are probably better off defining a single channel identifier for your table, or perhaps for this specific trigger, and then construct the payload message in such a way that you can easily extract the appropriate information for some response. If you cannot predict the values of NEW.my_label then it is plain impossible.

In your specific case you could have a channel name 'prices' and then do something like:

pg_notify('prices', format('%s: %s, NEW.my_label, row_to_json(NEW)::text));

The session with LISTEN prices will receive:

Asynchronous notification "prices" with payload "some_label: {new_row_to_json}" received from server process with PID 8448.

That is a rather silly response (why the "Asynchronous notification "channel" with payload ..." instead of just the payload and the PID?) but you can easily extract the relevant parts and work with those. Since you would have to manipulate the string anyway it is not a big burden to strip away all the PG overhead in one go, on a single channel, making management of the trigger actions far easier.

answered Aug 2, 2015 at 11:53

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

2 Comments

Stefan D Over a year ago

My application knows beforehand what values will be inserted. Different instances of my application care about different labels, when new data arrives the corresponding one needs to be notified.

Patrick Over a year ago

In that case it should work just fine by issuing LISTEN label in each separate instance and then regularly polling for incoming messages.

Collectives™ on Stack Overflow

Using row data in pg_notify trigger as channel name?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related