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

Groups > comp.databases.postgresql > #892 > unrolled thread

how to use NOTIFY to publish previous and new record

Back to article view | Back to comp.databases.postgresql

Contents

  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

#892 — how to use NOTIFY to publish previous and new record

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

[toc] | [next] | [standalone]

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web