CREATE OR REPLACE FUNCTION notify_col()
RETURNS trigger AS
$BODY$
BEGIN
PERFORM pg_notify('channel', row_to_json(NEW)::text);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
I'm trying to listen on channel, for any updates in the row (transformed with row_to_json()
). But I want pg_notify()
to send only updated values of the row, not the whole row.
Do I need an if
condition or is there is a better way to do this?
Best Answer
This sends a JSON array of columns that actually changed, with their new value:
db<>fiddle here
If works by converting
OLD
andNEW
to JSON and expanding each to a set of key/value pairs. Then join on the key and only keep columns (now rows) that actually changed. Finally aggregate into a JSON array.Note the
WHEN
condition to the trigger. Updates that do not actually change anything don't fire the trigger. (So the trigger is guaranteed to process actual changes.)If there can be more triggers consider an
AFTER
trigger to only report what's actually changed. And reconsider theWHEN
condition if triggers might add changes - which would circumvent this condition.Related:
Aside: consider this note in the manual: