Thread: TG_OP and undefined OLD values

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

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/static/sql-expressions.html#SYNTAX-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