How to send only updated values of a row?

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();

db<>fiddle here

If works by converting OLD and 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.

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 the WHEN condition if triggers might add changes - which would circumvent this condition.

Related:

• 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:


array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option.

StackExchange Database Administrators Q#225960, answer score: 4