Skip to content

Instantly share code, notes, and snippets.

@calebcase
Created July 23, 2014 23:14
Show Gist options
  • Save calebcase/2bde7e55803fd4b7cabf to your computer and use it in GitHub Desktop.
Save calebcase/2bde7e55803fd4b7cabf to your computer and use it in GitHub Desktop.
Possibly reusable with minor tweaks...
CREATE OR REPLACE FUNCTION notify_changes_on_create() RETURNS trigger
LANGUAGE plpgsql
AS $$BEGIN
PERFORM pg_notify('changes', '{"kind": "create", "data": ' || NEW.data::text || '}');
RETURN NULL;
END;$$;
CREATE OR REPLACE FUNCTION notify_changes_on_delete() RETURNS trigger
LANGUAGE plpgsql
AS $$BEGIN
PERFORM pg_notify('changes', '{"kind": "delete", "data": ' || OLD.data::text || '}');
RETURN NULL;
END;$$;
CREATE OR REPLACE FUNCTION notify_changes_on_update() RETURNS trigger
LANGUAGE plpgsql
AS $$BEGIN
PERFORM pg_notify('changes', '{"kind": "update", "data": [' || OLD.data::text || ', ' || NEW.data::text || ']');
RETURN NULL;
END;$$;
CREATE TABLE IF NOT EXISTS "user" (
email text NOT NULL,
data json NOT NULL,
PRIMARY KEY (email)
);
DROP TRIGGER IF EXISTS notify_changes_on_create ON "user";
CREATE TRIGGER notify_changes_on_create
AFTER INSERT
ON "user"
FOR EACH ROW
EXECUTE PROCEDURE notify_changes_on_create();
DROP TRIGGER IF EXISTS notify_changes_on_delete ON "user";
CREATE TRIGGER notify_changes_on_delete
AFTER DELETE
ON "user"
FOR EACH ROW
EXECUTE PROCEDURE notify_changes_on_delete();
DROP TRIGGER IF EXISTS notify_changes_on_update ON "user";
CREATE TRIGGER notify_changes_on_update
AFTER UPDATE
ON "user"
FOR EACH ROW
EXECUTE PROCEDURE notify_changes_on_update();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment