Re: [HACKERS] Odd plpgsql behaviour

[Mike Rylander]

On Mon, 15 Nov 2004 15:12:24 +0800, Christopher Kings-Lynne
[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Odd plpgsql behaviour

[Tom Lane]

Mike Rylander [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] Odd plpgsql behaviour

[Christopher Kings-Lynne]

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?

Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings