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.