[Resend; the previous one is stuck in moderation limbo, sorry]
I'm trying to write a trigger that only reacts if any but one or
two fields in a record is altered:
DECLARE
new_open_timeout timestamp;
BEGIN
-- NULL out insignificant changes
new_open_timeout := NEW.open_timeout;
NEW.open_timeout := NULL;
OLD.open_timeout := NULL;
IF NEW <> OLD THEN
-- react to significant change here
END IF;
NEW.open_timeout := new_open_timeout;
RETURN NEW;
END;
I get
WARNING: Error occurred while executing PL/pgSQL function updated_trigger
WARNING: line 9 at if
ERROR: NEW used in non-rule query doing SELECT foo()
where foo() updates a record.
Any suggestions?
The reasons I prefer this approach to comparing the significant fields
one at a time are (a) this is less prone to error in the face of
maintainers adding fields but not tweaking the trigger and (b) this
is a lot less typing.
P.S. FWIW, I originally tried code like:
DECLARE
masked_NEW foo%ROWTYPE;
masked_OLD foo%ROWTYPE;
BEGIN
masked_NEW := NEW;
masked_OLD := OLD;
-- NULL out insignificant changes
masked_NEW.open_timeout := NULL;
masked_OLD.open_timeout := NULL;
IF masked_NEW <> masked_OLD THEN
-- react to significant change here
END IF;
RETURN NEW;
END;
and got a "parse error near masked_NEW" with no line information. I'm
ASSuming it's the first := line, but I didn't dig in to it.
Thanks,
Barrie