Skip to main content
added 556 characters in body
Source Link
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

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. If your worry is enumerating the column names, then my advice is not to be that lazy. 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;

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. If your worry is enumerating the column names, then my advice is not to be that lazy. You can easily automate that by constructing a CREATE TRIGGER statement from the metadata in information_schema.columns.

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;
Source Link
Laurenz Albe
  • 56.4k
  • 4
  • 50
  • 82

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. If your worry is enumerating the column names, then my advice is not to be that lazy. You can easily automate that by constructing a CREATE TRIGGER statement from the metadata in information_schema.columns.