|
Home > Archive > PostgreSQL Discussion > August 2005 > TG_OP and undefined OLD values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
TG_OP and undefined OLD values
|
|
| Mike Nolan 2005-08-26, 11:23 am |
| I'm trying to write some code in a trigger that fires on both an insert
and an update.
At one point I need to update a column either on an insert or if the
value of the column has changed.
The following code fails because the OLD value is not defined:
if TG_OP = ''INSERT''
or (TG_OP = ''UPDATE'' and NEW.column1 != coalesce(OLD.column1,''--'')) then
column2 := ''CHANGED'';
end if;
Shouldn't OLD.column1 not even be evaluated when the other if statement in
the group in parentheses is false or when the earlier if statement is true?
Is there a way around this other than separating the code into two
independent if statements, duplicating the action statements?
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2005-08-26, 1:23 pm |
| Mike Nolan <nolan@gw.tssi.com> writes:
> The following code fails because the OLD value is not defined:
> if TG_OP = ''INSERT''
> or (TG_OP = ''UPDATE'' and NEW.column1 != coalesce(OLD.column1,''--'')) then
> column2 := ''CHANGED'';
> end if;
> Shouldn't OLD.column1 not even be evaluated when the other if statement in
> the group in parentheses is false or when the earlier if statement is true?
No. Postgres does not guarantee short-circuit evaluation --- see
http://www.postgresql.org/docs/8.0/...AX-EXPRESS-EVAL
In this particular case, even if the execution engine does things in the
order you want, you lose because plpgsql needs to plug in the parameter
values representing its variables before execution of the boolean
expression even begins.
So you'll have to break it into multiple IF commands.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
|
|
|
|