Skip to content

Instantly share code, notes, and snippets.

@Fonsan
Created December 21, 2023 22:44
Show Gist options
  • Save Fonsan/c367a2364588f19ad33d723c43ebbd39 to your computer and use it in GitHub Desktop.
Save Fonsan/c367a2364588f19ad33d723c43ebbd39 to your computer and use it in GitHub Desktop.
Postgres table changes listen notify
DROP TABLE IF EXISTS foo ;
CREATE TABLE foo(
id serial primary key,
body text
);
INSERT INTO foo (body) values ('blabal');
CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
rec record;
dat record;
channel TEXT;
delete_channel TEXT;
payload TEXT;
BEGIN
channel := TG_TABLE_NAME || '_';
CASE TG_OP
WHEN 'UPDATE' THEN
rec := NEW;
channel := channel || 'update_' || rec.id;
WHEN 'INSERT' THEN
rec := NEW;
channel := channel || 'insert';
WHEN 'DELETE' THEN
rec := OLD;
delete_channel := channel || 'delete_' || rec.id;
channel := channel || 'delete';
ELSE
RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
END CASE;
payload := json_build_object(
'timestamp',
CURRENT_TIMESTAMP,
'record',
row_to_json(rec)
);
PERFORM pg_notify(channel, payload);
if delete_channel IS NOT NULL then
PERFORM pg_notify(delete_channel, payload);
end if;
RETURN rec;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER foo_notify AFTER INSERT OR UPDATE OR DELETE ON foo
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();
CREATE OR REPLACE FUNCTION foo_listen() RETURNS VOID AS $$
DECLARE
row_id varchar;
BEGIN
FOR row_id in SELECT id::varchar FROM foo LOOP
execute format('LISTEN %I', 'foo_' || row_id);
execute format('LISTEN %I', 'foo_delete_' || row_id);
END LOOP;
LISTEN foo_insert;
LISTEN foo_delete;
END
$$ LANGUAGE plpgsql;
SELECT foo_listen() as output;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment