Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

Next:From: Jeff EckermannDate: 2003-05-30 13:58:53
Subject: Re: How to query multiple dbases efficiently?
Previous:From: Carlos OlivaDate: 2003-05-30 12:46:56
Subject: Re: FW: Blocking access to the database??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group