Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Event emitter for postgresql listen function
-- Function: notify_event()
-- DROP FUNCTION notify_event();
CREATE OR REPLACE FUNCTION notify_event()
RETURNS trigger AS
$BODY$
DECLARE
data json;
notification json;
BEGIN
-- Convert the old or new row to JSON, based on the kind of action.
-- Action = DELETE? -> OLD row
-- Action = INSERT or UPDATE? -> NEW row
IF (TG_OP = 'DELETE') THEN
data = row_to_json(OLD);
ELSE
data = row_to_json(NEW);
END IF;
-- Contruct the notification as a JSON string.
-- Execute pg_notify(channel, notification)
PERFORM pg_notify('events_'||TG_TABLE_NAME,'{"action":"'||TG_OP||'","data":'||data::text||'}');
-- Result is ignored since this is an AFTER trigger
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION notify_event()
OWNER TO merlin;
-- Trigger: ally_target_notify_event on ally_target
-- DROP TRIGGER ally_target_notify_event ON ally_target;
CREATE TRIGGER ally_target_notify_event
AFTER INSERT OR UPDATE OR DELETE
ON ally_target
FOR EACH ROW
EXECUTE PROCEDURE notify_event();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.