Postgres LISTEN / NOTIFY with Node.js

Postgres 在 9.1 之后推出 LISTEN / NOTIFY

创建一个数据表 foo

CREATE TABLE foo (id serial primary key, name varchar);

创建一个 存贮过程

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

创建触发器 当存在插入时候

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

使用 node js 监听

const { Client } = require('pg');

const constr = {
    user: 'chris', password: 'postgres', host: '127.0.0.1', port: 5432, database: 'postgres'
};
const client = new Client(constr)

client.connect().then(() => {
    console.log('Connected to PostGreSQL database');
    client.query('LISTEN watchers');
}).catch((err) => {
    console.error('Error connecting to PostGreSQL database', err);
});

client.on('notification', (notification) => {
    console.log('Received notification:', notification.payload);
});

process.on('SIGINT', () => {
    client.end();
    process.exit(0);
});

触发通知

• 插入一条数据

INSERT INTO "public"."foo" ("name") VALUES ('2') RETURNING *

查看看效果