Within a trigger function, how to get which fields are being updated

If a “source” doesn’t “send an identifier”, the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly.

If the “source” is identifiable by any session information function, you can work with that. Like:

NEW.column = session_user;

Unconditionally for every update.

General Solution

I found a way how to solve the original problem.

Set the column to a default value if it’s not targeted in an UPDATE (not in the SET list). Key element is a per-column trigger introduced with PostgreSQL 9.0 – a column-specific trigger using the UPDATE OFcolumn_name clause. The manual:

The trigger will only fire if at least one of the listed columns is
mentioned as a target of the UPDATE command.

That’s the only simple way I found to distinguish whether a column was updated with a new value identical to the old, versus not updated at all.

One could also parse the text returned by current_query(). But that seems cumbersome, tricky and unreliable.

Trigger functions

I assume a column source defined NOT NULL.

Step 1: Set source to NULL if unchanged:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source = OLD.source THEN
      NEW.source := NULL;      -- "impossible" value (source is NOT NULL)
   END IF;

   RETURN NEW;
END
$func$;

Step 2: Revert to old value. Trigger will only be fired, if the value was actually updated (see below):

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := OLD.source;
   END IF;

   RETURN NEW;
END
$func$;

Step 3: Now we can identify the lacking update and set a default value instead:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := 'UPDATE default source';  -- optionally same as column default
   END IF;

   RETURN NEW;
END
$func$;

Triggers

The trigger for Step 2 is fired per column!

CREATE TRIGGER upbef_step1
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE TRIGGER upbef_step2
  BEFORE UPDATE OF source ON tbl             -- key element!
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step2();
    
CREATE TRIGGER upbef_step3
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step3();

db<>fiddle here

Trigger names are relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

The procedure could be simplified with something like “per-not-column triggers” or any other way to check the target-list of an UPDATE in a trigger. But I see no handle for this, currently (unchanged as of Postgres 14).

If source can be NULL, use any other “impossible” intermediate value and check for NULL additionally in trigger function 1:

IF OLD.source IS NOT DISTINCT FROM NEW.source THEN
    NEW.source := '#impossible_value#';
END IF;

Adapt the rest accordingly.