37 lines
823 B
PL/PgSQL
37 lines
823 B
PL/PgSQL
create table pg_notifications_queue (message jsonb);
|
|
|
|
create function notify_event()
|
|
returns trigger as $$
|
|
declare
|
|
msg json;
|
|
payload json;
|
|
id uuid;
|
|
|
|
begin
|
|
if (tg_op = 'DELETE') then
|
|
id = old.id;
|
|
else
|
|
id = new.id;
|
|
end if;
|
|
|
|
msg = jsonb_build_object(
|
|
'id', gen_random_uuid(),
|
|
'table', tg_table_name,
|
|
'op', tg_op,
|
|
'trigger', tg_argv[0],
|
|
'payload', json_build_object('id', new.id, 'source', tg_argv[1], 'target', tg_argv[2])
|
|
);
|
|
|
|
insert into pg_notifications_queue (message) values (msg::jsonb);
|
|
|
|
perform pg_notify('events', msg::text);
|
|
|
|
return null;
|
|
end $$ language plpgsql;
|
|
|
|
-- create trigger <table>_notify_event
|
|
-- after update on <table>
|
|
-- for each row
|
|
-- when (old.<something> is distinct from new.<something>)
|
|
-- execute procedure notify_event('kramdown', '<something>', '<html_something>');
|