how to run trigger on table on postgresql except one column

Question

I'm using postgresql 13/14. lets say I have a table with column A,B,C,D,E I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for update actually, the trigger function is just a bit generic) it has run good for now. problem is, column E is only for checking, and it's not needed to run the trigger if it's updated.

my trigger function, is now like this:

CREATE FUNCTION public.fnc_check()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    if NEW.A<>OLD.A and NEW.B<>OLD.B and NEW.C<>OLD.C and NEW.D<>OLD.D then
         -- do something
    end if;

    RETURN NEW;
END;
$BODY$;

problem is, on my real table, there are like 20-30 columns and it's a hassle to make if for every columns except for column E. so, is there a way to make sure the trigger only run for column A,B,C,D and not E (without using if for each other columns except E) ?

thx very much

Answer 1

Your code has no connection to your verbal description, and I will go with that description, because it actually makes sense.

Perhaps you mean

CREATE TRIGGER ... UPDATE OF a, b, c, d ON tab ...

That will call the trigger function only when one of the named columns has changed. You can easily automate that by constructing a CREATE TRIGGER statement from the metadata in information_schema.columns, but I understand that this is cumbersome if you have frequent changes to the table definition.

You could use an ugly trick like that:

DECLARE
   old_e timestamp with time zone;  -- replace with the correct type
   new_e timestamp with time zone;  -- replace with the correct type
   skip_execution boolean;
BEGIN
   old_e := OLD.e;
   OLD.e := NULL;
   new_e := NEW.e;
   NEW.e := NULL;

   skip_execution := OLD IS NOT DISTINCT FROM NEW;

   OLD.e := old_e;
   NEW.e := new_e;

   IF NOT skip_execution THEN
      /* the rest of your trigger code */
   END IF;

   RETURN /* something appropriate */
END;

Answer 2

Depending on your setup you may be able to get away with the following style solution. In my case below I have want it to update a timestamp on every update to each row, except if a specific field (d_last_statement_run) has been updated. So in my trigger I simply check to make sure that is the same before allowing the trigger to take action. Please note the use of Coalesce(field, 'defaultvalue') in the comparison. This is required because postgresql can't compare NULL. So if the field your are trying to exclude updates on is null, it would not work in the trigger check.

CREATE OR REPLACE TRIGGER row_mod_on_mytable_trigger
BEFORE UPDATE ON mytable
FOR EACH ROW
WHEN (coalesce(OLD.d_last_statement_run, statement_timestamp()) = coalesce(NEW.d_last_statement_run, statement_timestamp()))
EXECUTE PROCEDURE update_timestamp_function();

And of course the stored procedure that is called:

 -- to run this on pgAdmin remove the \ from the $
CREATE OR REPLACE FUNCTION update_timestamp_function() RETURNS TRIGGER
    LANGUAGE plpgsql
AS
\$\$
BEGIN
    IF tg_op = 'UPDATE' THEN
        new.d_updated_timestamp = STATEMENT_TIMESTAMP();
        RETURN new;
    END IF;
END;
\$\$;

Triggers are documented here: https://www.postgresql.org/docs/current/sql-createtrigger.html