Node-RED listen PostgreSQL in real-time

Question

I need to listen PostgreSQL on changes in real-time with Node-RED. How can I do this?

I created trigger on new record in the table and notify this to 'changes' channel.

CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify('changes', TG_TABLE_NAME || ',id,' || NEW.id );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watched_table_trigger AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

But I don't know how to listen it from Node-RED. Could you help me please? Maybe I can do it differently?

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

Have a look at this previous SO question:

MQTT Client subscribe to PostgreSQL DB Changes

It looks like Postgress supports Python based triggers which could be used to send a MQTT message which Node-RED could easily subscriber to.

edited May 23 '17 at 12:25

Community

1
1

answered Dec 26 '16 at 17:33

hardillb

54,545
11
67
105

Hm.. I'll try it as an alternative. Thank you. I found a good solution for yourself. I'll add my solution soon – GatoPresto Dec 28 '16 at 12:54

score 2 · Accepted Answer · answered Dec 28 '16 at 13:03

I found a good solution for yourself with WebSocket. Look at example below:

var pg = global.get('pg'),
    WebSocket = global.get('ws'),
    config = {
        user: 'user',
        password: 'user',
        host: 'somehost',
        port: 1234,
        database: 'somedb'
    },
    client = new pg.Client(config);

client.connect(function(err) {
    if (err) node.error(err);

    client.on('notification', function(msg) {
        node.send(msg);
    });

    var query = client.query("LISTEN changes");
});

delete msg._session;
return msg;

Post your solution, I really want to know more ways to solve this.

Could you please provide the entire flow? At the moment I´m using [Python as a middleware](https://medium.com/towardsdev/simple-event-notifications-with-postgresql-and-python-398b29548cef) to provide the PG trigger messages per MQTT to Node-RED. Thank you very much in advance! — Andre S., Feb 24 '22 at 07:08

Node-RED listen PostgreSQL in real-time

2 Answers2