From: | Barrie Slaymaker <barries(at)slaysys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | IF NEW <> OLD THEN ... vs. NEW used in non-rule query? |
Date: | 2003-05-30 12:56:18 |
Message-ID: | 20030530125615.GA18257@sizzle.whoville.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
[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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Eckermann | 2003-05-30 13:58:53 | Re: How to query multiple dbases efficiently? |
Previous Message | Carlos Oliva | 2003-05-30 12:46:56 | Re: FW: Blocking access to the database?? |