how to use NOTIFY to publish previous and new record

[Alexander Mills]

Can anyone describe how I might be able to use NOTIFY with postgres, to send both the previous record and the new record, for an update to a table record?

something like:

NOTIFY <namespace>
{prev: {}}
{new: {}}

some example with code (node.js or golang ideal) would really help me so much, never used NOTIFY before and find examples only to be obtuse,

-alex

[Christian Barthel]

There are certainly a lot of different approaches. One
possibility (based on the information above) might be to send an
additional payload (max 8000 Bytes) with NOTIFY. To send the
previous and new row, you can use a PostgreSQL Trigger (in
PL/pgSQL):

CREATE TABLE abc (
i integer
);


CREATE FUNCTION abc_notify()
RETURNS trigger AS $$
DECLARE
mesg text;
BEGIN
-- message as text:
select into mesg
concat (OLD, ' -> ', NEW);

-- NOTIFY channel, payload
PERFORM pg_notify('channel', mesg);

RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER abc_notify BEFORE UPDATE ON abc
FOR EACH ROW EXECUTE PROCEDURE abc_notify();

See the PostgreSQL documentation [1] for limitations and further
details.

[1] https://www.postgresql.org/docs/12/sql-notify.html

--
Christian Barthel <b...@online.de>