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

Поиск
Список
Период
Сортировка
От Barrie Slaymaker
Тема IF NEW <> OLD THEN ... vs. NEW used in non-rule query?
Дата
Msg-id 20030530125615.GA18257@sizzle.whoville.com
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: "Carlos Oliva"
Дата:
Сообщение: Re: FW: Blocking access to the database??
Следующее
От: Jeff Eckermann
Дата:
Сообщение: Re: How to query multiple dbases efficiently?