0

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

3
  • 1
    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.
    – user1822
    Commented Oct 26, 2022 at 11:00
  • 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).
    – Akina
    Commented Oct 26, 2022 at 11:14
  • 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
    – Fire
    Commented Oct 28, 2022 at 1:23

1 Answer 1

0

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;
2
  • 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
    Commented Oct 28, 2022 at 1:28
  • 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" ?
    – Fire
    Commented Oct 28, 2022 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.