NOTIFY e LISTEN no PostgreSQL

Função Trigger (Trigger Function) genérica para avisar modificações (INSERT, UPDATE E DELETE) em tabelas no PostgreSQL

1. Esta função envia uma mensagem NOTIFY para o canal "schema.nome_da_tabela"
2. O conteúdo da mensagem é um JSON
3. O JSON contém 2 campos, "old" e "new"
4. old contém o conteúdo da linha em seu estado antigo em formato JSON
5. new contém o conteúdo novo da linha em seu estado novo em formato JSON

[code language="sql"]

CREATE OR REPLACE FUNCTION public.notify_with_json() RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS $$ DECLARE old_json JSONB; new_json JSONB; return_record RECORD; BEGIN

CASE TG_OP WHEN 'INSERT' THEN SELECT jsonb_build_object('old',NULL) INTO old_json; SELECT jsonb_build_object('new',row_to_json(NEW)) INTO new_json; return_record = NEW; WHEN 'UPDATE' THEN SELECT jsonb_build_object('old',row_to_json(OLD)) INTO old_json; SELECT jsonb_build_object('new',row_to_json(NEW)) INTO new_json; return_record = NEW; WHEN 'DELETE' THEN SELECT jsonb_build_object('old',row_to_json(OLD)) INTO old_json; SELECT jsonb_build_object('new',NULL) INTO new_json; return_record = OLD; END CASE;

PERFORM pg_notify(TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME,(old_json || new_json)::TEXT);

RETURN return_record; END; $$ [/code]

Uma outra versão da função, mas que também envia o nome da tabela com o schema dentro do JSON.

[code]

CREATE FUNCTION public.notify_with_json() RETURNS trigger LANGUAGE 'plpgsql' COST 100 STABLE NOT LEAKPROOF AS $BODY$

DECLARE old_json JSONB; new_json JSONB; table_name JSONB; return_record RECORD; BEGIN SELECT jsonb_build_object('table_name',TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME) INTO table_name; CASE TG_OP WHEN 'INSERT' THEN SELECT jsonb_build_object('old',NULL) INTO old_json; SELECT jsonb_build_object('new',row_to_json(NEW)) INTO new_json; return_record = NEW; WHEN 'UPDATE' THEN SELECT jsonb_build_object('old',row_to_json(OLD)) INTO old_json; SELECT jsonb_build_object('new',row_to_json(NEW)) INTO new_json; return_record = NEW; WHEN 'DELETE' THEN SELECT jsonb_build_object('old',row_to_json(OLD)) INTO old_json; SELECT jsonb_build_object('new',NULL) INTO new_json; return_record = OLD; END CASE;

PERFORM pg_notify(TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME,(table_name || old_json || new_json)::TEXT);

RETURN return_record; END;

$BODY$;

ALTER FUNCTION public.notify_with_json() OWNER TO general_access;

[/code]

Chamando a Função Trigger acima após um evento em uma tabela

[code language="sql"] CREATE TRIGGER sua_tabela_notify_with_json AFTER INSERT OR DELETE OR UPDATE ON seu_schema.sua_tabela FOR EACH ROW EXECUTE PROCEDURE public.notify_with_json(); [/code]