Postgres Notifications

Postgres Notifications

-- send notification to channel 'planet' with data 'bob'
select pg_notify('planet', 'bob')


-- notify statement (faster than per row)

CREATE OR REPLACE FUNCTION public.notify_statement_trigger()
  RETURNS trigger AS
$BODY$
begin
   PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
   return null;
end $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.notify_statement_trigger()
  OWNER TO postgres;

--
-- trigger to createa statement level notification
CREATE TRIGGER XXXX_notify_statement AFTER INSERT OR UPDATE OR DELETE ON XXXX
FOR EACH STATEMENT EXECUTE PROCEDURE notify_statement_trigger();

--
-- trigger to create row level notification
-- 'ptype', 'v0', 'v1','v2','v3' are the columns in the table which we want to report on in this example..
CREATE TRIGGER policy_notify
  AFTER INSERT OR UPDATE OR DELETE
  ON public.policy
  FOR EACH ROW
  EXECUTE PROCEDURE public.notify_trigger('ptype', 'v0', 'v1','v2','v3');





-- sends row level notification to channel db_notifications, the notification data is packaged in json and includes the passed columns...
-- DROP FUNCTION public.notify_trigger();
CREATE OR REPLACE FUNCTION public.notify_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items TEXT[];
BEGIN
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
  
  -- Get required fields
  FOREACH column_name IN ARRAY TG_ARGV LOOP
    EXECUTE format('SELECT $1.%I::TEXT', column_name)
    INTO column_value
    USING rec;
    payload_items := array_append(payload_items, '"' || replace(column_name, '"', '\"') || '":"' || replace(column_value, '"', '\"') || '"');
  END LOOP;

  -- Build the payload
  payload := ''
              || '{'
              || '"timestamp":"' || CURRENT_TIMESTAMP                    || '",'
              || '"operation":"' || TG_OP                                || '",'
              || '"schema":"'    || TG_TABLE_SCHEMA                      || '",'
              || '"table":"'     || TG_TABLE_NAME                        || '",'
              || '"data":{'      || array_to_string(payload_items, ',')  || '}'
              || '}';

  -- Notify the channel
  PERFORM pg_notify('db_notifications', payload);
  
  RETURN rec;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.notify_trigger()
  OWNER TO postgres;