Odd plpgsql behaviour

On Mon, 15 Nov 2004 15:12:24 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

On 7.4:

This is what we wanted to do:

IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.name != OLD.name) THEN
EXECUTE x;
END IF;

However, we had to write it like this:

IF TG_OP = 'INSERT' THEN
EXECUTE x;
ELSIF TG_OP = 'UPDATE' AND NEW.name != OLD.name THEN
EXECUTE x;
END IF;

Because in the first case it would complain that OLD.name wasn't
defined, if the trigger was NOT an update.

OK, but the second case works??!?! Is this a weird peculiarity of the
pl/pgsql lazy evaluation rules? Why doesn't the first one work if the
second one does?

IIRC, the reason for this is that the entire IF test is passed to the
SQL engine as a SELECT statement after replacing the TG_* identifiers
with their respective values.

Your first example is essentially

IF (SELECT (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.name !=
OLD.name) IS TRUE) ...

In this case, since OLD.name does not exist during INSERT it cannot be
replaced. Perhaps someone else can shed a little more light on this.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer

Mike Rylander <mrylander@gmail.com> writes:

IIRC, the reason for this is that the entire IF test is passed to the
SQL engine as a SELECT statement after replacing the TG_* identifiers
with their respective values.
In this case, since OLD.name does not exist during INSERT it cannot be
replaced. Perhaps someone else can shed a little more light on this.

Nope, that's about all there is to say about it ...

regards, tom lane