@itcoordinadora/pg-listen-new v1.0.2

node-red-contrib-re-postgres with pg-listen

Nodo NODERED con conexion implementando pg-listen

Install

Run the following command in the root directory of your Node-RED install

npm install @itcoordinadora/pg-listen-new

Overview

This add-on, allows to listen to PostgreSQL pg_notify mechanism.

The node takes two parameters :

• postgresdb : The PostgreSQL connection configuration
• channel : The channel name specified in the pg_notify command

PostgreSQL sample code

1. Create a base table:

   CREATE TABLE realtime
   (
       id INTEGER DEFAULT nextval('realtime_id_seq'::regclass) NOT NULL,
       title CHARACTER VARYING(128),
       PRIMARY KEY (id)
   );

2. Create a trigger on the table:

   CREATE TRIGGER "updated_realtime_trigger"
     BEFORE INSERT OR DELETE OR UPDATE ON realtime
     FOR EACH ROW
   EXECUTE PROCEDURE notify_realtime()

3. Create a trigger function:

   CREATE FUNCTION public.notify_realtime()
       RETURNS trigger
       LANGUAGE 'plpgsql'
       COST 100.0
       VOLATILE NOT LEAKPROOF 
   AS $BODY$
   
   BEGIN
       PERFORM pg_notify('addedrecord', '' || row_to_json(NEW));
       RETURN NEW;
   END;
   $BODY$;

Result

The node will produce a message like that :

{"name":"notification","length":47,"processId":16147,"channel":"addedrecord","payload":{"id":2,"title":"plopcsd"}}

All fields are generated by Postgres with payload being the content of the table row.