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?
This sends a JSON array of columns that actually changed, with their new value:
CREATE OR REPLACE FUNCTION notify_col() RETURNS trigger AS $func$ BEGIN PERFORM pg_notify('channel' , (SELECT json_agg(n)::text FROM json_each_text(to_json(OLD)) o JOIN json_each_text(to_json(NEW)) n USING (key) WHERE n.value IS DISTINCT FROM o.value)); RETURN NEW; END $func$ LANGUAGE plpgsql; CREATE TRIGGER tbl_upd_aft BEFRE UPDATE ON tbl FOR EACH ROW WHEN (OLD IS DISTINCT FROM NEW) -- optional EXECUTE procedure notify_col();
If works by converting
NEW 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.
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 the
WHEN condition if triggers might add changes – which would circumvent this condition.
- Is it possible to dynamically loop through a table’s columns?
- Trigger with multiple WHEN conditions
- How do I (or can I) SELECT DISTINCT on multiple columns? (about empty updates)
Aside: consider this note in the manual:
row_to_jsonhave the same behavior as
to_jsonexcept for offering a pretty-printing option.