Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #893

Re: how to use NOTIFY to publish previous and new record

Show key headers only | View raw

Alexander Mills <alexander.d.mills@gmail.com> writes:

> 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,

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 <bch@online.de>

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

how to use NOTIFY to publish previous and new record Alexander Mills <alexander.d.mills@gmail.com> - 2020-12-25 17:27 -0800
  Re: how to use NOTIFY to publish previous and new record Christian Barthel <bch@online.de> - 2020-12-26 21:50 +0100

csiph-web