Question 114252 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to exclude columns from OLD and NEW object in PostreSql trigger function

I have a scenario, where a trigger function is activated before an update of some sort. And in order for the condition to be met one of the columns must be excluded from the "OLD" and "NEW" objects.

CREATE FUNCTION example_function() RETURNS trigger AS
    $example_function$
BEGIN
    IF
(OLD <> NEW) IS TRUE THEN
//Do something here
END IF;
RETURN NEW;
END;
$example_function$
LANGUAGE plpgsql;


CREATE TRIGGER example_function
    BEFORE UPDATE
    ON some_table
    FOR EACH ROW
    EXECUTE PROCEDURE example_function();

The problem is that the table has above 50 columns and it would be quite hard to type out or maintain afterwards.

The column i want to exclude – lets say modified_date will meet the condition of being different every time, however how can it be excluded from the check completely?

(OLD <> NEW exclude modified_date ) IS TRUE THEN – something similar should happen logically

Tags:

2

Answers


  1. 0

    Perhaps you could use code like this, where tab is the table with the trigger:

    DECLARE
   oldrow tab := OLD;
   newrow tab := NEW;
BEGIN
   oldrow.modified_date := NULL;
   newrow.modified_date := NULL;

   IF oldrow IS DISTINCT FROM newrow THEN
      /* whatever */
   END IF;

   RETURN NEW;
END
    Login or Signup to reply.
  2. 0

    You can compare JSON representations of the records excluding selected columns:

        ...
    if to_jsonb(new)- 'modified_date' <> to_jsonb(old)- 'modified_date' then
    ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search