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 по дате отправления: