PL/PGSQL: why IF test the whole condition before failing or not?

Suporte PK <fknoedt@gmail.com> writes:

IF TG_OP = 'UPDATE' AND OLD.field != NEW.field THEN
...
The question is: if the trigger was not fired by an UPDATE event,
shouldn't it make the first test and then ignore the rest of the condition?

No. This is a very very common error. The behavior is not as
short-circuity as you'd think. Break it into two IFs.

regards, tom lane

On 2009-08-18, Suporte PK <fknoedt@gmail.com> wrote:

Hi list,

I'm having trouble with - believe me! - the IF operator on a PL/PGSQL
function used by a trigger.

I'm using one unique function to process the three triggers events
(delete, update and insert), but when I reference OLD or NEW on a IF
CONDITION, I get an error even when testing BEFORE if it's a UPDATE
event or not.

example:

IF TG_OP = 'UPDATE' AND OLD.field != NEW.field THEN
...
END IF;

The question is: if the trigger was not fired by an UPDATE event,
shouldn't it make the first test and then ignore the rest of the condition?

I know that some languages work like this (testing the whole condition)
while others don't, but I searched for an alternative without success.

Any advice would be much appreciated!

as you see a few more error messages (or unlike me get as far as
http://www.postgresql.org/docs/8.4/static/plpgsql-expressions.html
in the manual and understand it)

you'll come to understand that every /expression/ in plpgsql is
translated into a select, this can be exploited to simplifiy code.

avariable = somecolumn FROM atable WHERE someother=foo;

but has its down sides too:

SELECT TG_OP = 'UPDATE' AND OLD.field != NEW.field;

doesn't pass go with NEW undefined.