IF NEW <> OLD THEN ... vs. NEW used in non-rule query?

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

Browse pgsql-general by date

  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??