Skip to content

Instantly share code, notes, and snippets.

@tuan9x

tuan9x/1_triggers.sql

Forked from fritzy/1_triggers.sql
Created March 9, 2021 18:15
Show Gist options
  • Save tuan9x/ffe8e21a1b25fc839ea0270c3b78831d to your computer and use it in GitHub Desktop.
Save tuan9x/ffe8e21a1b25fc839ea0270c3b78831d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Nathan Fritz revised this gist Apr 6, 2015. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions 2_listen_table_update.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,6 @@
    var pg = require ('pg');
    var pgConString = "postgres://localhost/fritzy"

    pg.connect(pgConString, function(err, client) {
    pg.connect("postgres://localhost/fritzy", function(err, client) {
    if(err) {
    console.log(err);
    }
  2. Nathan Fritz revised this gist Apr 6, 2015. 3 changed files with 0 additions and 0 deletions.
    0 triggers.sql → 1_triggers.sql
    View file
    File renamed without changes.
    0 listen_table_update.js → 2_listen_table_update.js
    View file
    File renamed without changes.
    0 io.txt → 3_output.txt
    View file
    File renamed without changes.
  3. Nathan Fritz revised this gist Apr 6, 2015. 2 changed files with 42 additions and 0 deletions.
    23 changes: 23 additions & 0 deletions io.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    > 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
    -========-
    19 changes: 19 additions & 0 deletions listen_table_update.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    var pg = require ('pg');
    var pgConString = "postgres://localhost/fritzy"

    pg.connect(pgConString, 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");
    });
  4. Nathan Fritz created this gist Apr 6, 2015.
    23 changes: 23 additions & 0 deletions triggers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,23 @@
    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();