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

Question

Is this possible? I'm interested in finding out which columns were specified in the UPDATE request regardless of the fact that the new value that is being sent may or may not be what is stored in the database already.

The reason I want to do this is because we have a table that can receive updates from multiple sources. Previously, we weren't recording which source the update originated from. Now the table stores which source has performed the most recent update. We can change some of the sources to send an identifier, but that isn't an option for everything. So I'd like to be able to recognize when an UPDATE request doesn't have an identifier so I can substitute in a default value.

Answer 1

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. The column will be set to a default value in any update where the column is not updated (not in the SET list of the UPDATE).

Key element is a per-column trigger introduced in PostgreSQL 9.0 - a column-specific trigger using the UPDATE OFcolumn_name clause.

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 tricky and unreliable.

Trigger functions

I assume a column col defined NOT NULL.

Step 1: Set col to NULL if unchanged:

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

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

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 AS
$func$
BEGIN
   IF NEW.col IS NULL THEN
      NEW.col := OLD.col;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

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 AS
$func$
BEGIN
   IF NEW.col IS NULL THEN
      NEW.col := 'default value';
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

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

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.

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

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

Adapt the rest accordingly.