Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Get table change notifications from Postgres as JSON
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();
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");
});
> INSERT INTO users (username) values ('fritzy');
*========*
type INSERT
id 1
table users
-========-
> UPDATE users SET email='fritzy@andyet.com' WHERE id=1;
*========*
type UPDATE
id 1
table users
-========-
> DELETE FROM users WHERE id=1;
*========*
type DELETE
id 1
table users
-========-

yonirab commented Sep 2, 2015

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment