Get table change notifications from Postgres as JSON

1_triggers.sql

CREATE OR REPLACE FUNCTION table_update_notify() RETURNS trigger AS $$
DECLARE
  id bigint;
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    id = NEW.id;
  ELSE
    id = OLD.id;
  END IF;
  PERFORM pg_notify('table_update', json_build_object('table', TG_TABLE_NAME, 'id', id, 'type', TG_OP)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER users_notify_update ON users;
CREATE TRIGGER users_notify_update AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE table_update_notify();

DROP TRIGGER users_notify_insert ON users;
CREATE TRIGGER users_notify_insert AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE table_update_notify();

DROP TRIGGER users_notify_delete ON users;
CREATE TRIGGER users_notify_delete AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE table_update_notify();

2_listen_table_update.js

var pg = require ('pg');

pg.connect("postgres://localhost/fritzy", function(err, client) {
    if(err) {
        console.log(err);
    }
    client.on('notification', function(msg) {
        if (msg.name === 'notification' && msg.channel === 'table_update') {
            var pl = JSON.parse(msg.payload);
            console.log("*========*");
            Object.keys(pl).forEach(function (key) {
                console.log(key, pl[key]);
            });
            console.log("-========-");
        }
    });
    client.query("LISTEN table_update");
});

3_output.txt

> INSERT INTO users (username) values ('fritzy');

*========*
type INSERT
id 1
table users
-========-

> UPDATE users SET email='[email protected]' WHERE id=1;

*========*
type UPDATE
id 1
table users
-========-

> DELETE FROM users WHERE id=1;

*========*
type DELETE
id 1
table users
-========-

In order for the node.js listener connection to stay permanently open, you need something like this:

var client = new Client(pgConnectionString);
client.connect();
client.query('LISTEN "table_update"');
client.on('notification', function(data) {
});

See brianc/node-postgres#74, and http://lheurt.blogspot.co.il/2011/11/listen-to-postgresql-inserts-with.html.