PostgreSQL - Determine which columns were updated

Question

I have a table with many columns, one of which is a lastUpdate column. I am writing a trigger in plpgsql for Postgres 9.1, that should set a value for lasUpdate upon an UPDATE to the record.

The challenge is to exclude some pre-defined columns from that trigger; Meaning, updating those specific columns shouldn't affect the lastUpdate value of the record.

Any advise?

Answer 1

In PostgreSQL you can access the previous value using OLD. and the new ones using NEW. aliases. There is even a specific example in the docs for what you need:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE PROCEDURE check_account_update();

Answer 2

I know it is too old question, but I found myself with the same need and I managed to do it with a trigger using the information_schema.colmns table.

I attach here the possible solution where the only parameters to edit would be the TIMEUPDATE_FIELD and EXCLUDE_FIELDS in the trigger function check_update_testtrig():

CREATE TABLE testtrig
(
  id bigserial NOT NULL,
  col1 integer,
  col2 integer,
  col3 integer,
  lastupdate timestamp not null default now(),
  lastread timestamp,
  CONSTRAINT testtrig_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);


CREATE OR REPLACE FUNCTION check_update_testtrig()
  RETURNS trigger AS
$BODY$

  DECLARE
  TIMEUPDATE_FIELD text := 'lastupdate';
  EXCLUDE_FIELDS text[] := ARRAY['lastread'];
  PK_FIELD text := 'id';
  ROW_RES RECORD;
  IS_DISTINCT boolean := false;
  COND_RES integer := 0;

  BEGIN
  FOR ROW_RES IN 
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_schema = TG_TABLE_SCHEMA
    AND table_name = TG_TABLE_NAME
    AND column_name != TIMEUPDATE_FIELD
    AND NOT(column_name = ANY (EXCLUDE_FIELDS))
  LOOP
    EXECUTE 'SELECT CASE WHEN $1.' || ROW_RES.column_name || ' IS DISTINCT FROM $2.' || ROW_RES.column_name || ' THEN 1 ELSE 0 END'
      INTO STRICT COND_RES
      USING NEW, OLD;
    IS_DISTINCT := IS_DISTINCT OR (COND_RES = 1);
  END LOOP;
  IF (IS_DISTINCT)
  THEN
    EXECUTE 'UPDATE ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ' SET ' || TIMEUPDATE_FIELD || ' = now() WHERE ' || PK_FIELD || ' = $1.' || PK_FIELD
      USING NEW;
  END IF;
  RETURN NEW;

  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_update_testtrig
  AFTER UPDATE
  ON testtrig
  FOR EACH ROW
  EXECUTE PROCEDURE check_update_testtrig();

Answer 3

Looking to your question and your comment on the answer of Jakub Kania, would I say that part of the solution is that you will create an extra table.

The issue is that constraints on columns should only apply the functioning of the column itself, it should not affect the values of other columns in the table. Specifying which columns should influence the status 'lastUpdate' is imo business logic. The idea which columns should have impact on the value of the status column 'lastUpdate' changes along with the business, not with the table design. Therefor should the solution imo consist of a table in combination with a trigger.

I would add a table with a column for a list of columns (column can be of type array) that can be used in a trigger on the table like described by Jakub Kania. If the default behaviour should be that a new column has to change the value of the column 'lastUpdate', then would I create the trigger so that it only lists names of columns that do not change the value of 'lastUpdate'. If the default behaviour is to not change the value of the column 'lastUpdate',then would I advise you to add the name of the column to the list of columns in case the members in the list would change the value of the column 'lastUpdate'.

If the table column is within the list of columns then should it update the field lastUpdate.