Path: csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Christian Barthel <bch@online.de>
Newsgroups: comp.databases.postgresql
Subject: Re: how to use NOTIFY to publish previous and new record
Date: Sat, 26 Dec 2020 21:50:09 +0100
Lines: 52
Message-ID: <87tus8xpfi.fsf@x230a3.onfire.org>
References: <4cd43e08-e7d9-4360-8180-8ad54df0b572n@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain
X-Trace: individual.net mqvQaFbsKNWbDNCgTCl1ewPwwNNGutkIlSd1Tx1vEQ3ibhCHzA
Cancel-Lock: sha1:kz5/pEpaOjCy3WPsuB+5Ad+vvSo= sha1:MP72wct4B/5APxNiYQO1NYh3/84=
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (berkeley-unix)
X-Gpg-Key: https://onfire.org/bch/pgp/pubkey.asc
X-Archive: expiry=7
Xref: csiph.com comp.databases.postgresql:893

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>