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

• user1822: A trigger doesn't "run for a column" it is fired for a row (or a statement). The new and old record will always contain all columns. You can make the trigger only fire if E is not changed (or changed) but that's about it.
• Akina: except for column E Does this means "If column E is changed then do not perform an action"? or "If column E is the only column which is changed then do not perform an action"? or something else? Anycase you'd test what column(s) is altered and select according option (perform or exit).
• Fire: yes. if column E is changed, then do not perform the trigger. All other columns should run the trigger. and like I said, of course I can always do the IF and stated the columns 1 by 1, but it took efforts, and I'm wondering if there's any shortcut to do this

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;

• Fire: sorry, the code was actually for Insert, Update , and Delete. but I just updated it so that it would only for update trigger to avoid confusion. now onto the problem is that the trigger should run for all columns except E, so I'm wondering if there's a shortcut for that, because stating the columns 1 by 1 seems to not efficient as only E is different
• Fire: and, yes, it could be used by stating the columns 1 by 1 on the create trigger, and getting from the script, but again, if there's an addition of columns, then the create trigger would have to be recreated. What I was wondering if there's an easy syntax to help making an exception of E. lets say like : "IF NEW(Except E)<>OLD (Except E) Then" ?

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