1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • how to use NOTIFY to publish previous and new record

    From Alexander Mills@21:1/5 to All on Fri Dec 25 17:27:20 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Christian Barthel@21:1/5 to Alexander Mills on Sat Dec 26 21:50:09 2020
    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>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)