Thread: boolean short-circuiting in plpgsql

Hi everyone,

I may be missing something obvious, but it seems like the advice in
4.2.12 on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html
doesn't seem to apply in plpgsql.

I have a table that I want to use a trigger on when either a new row
is inserted or at least one of two particular columns is updated.
This fails on insert:

begin
    if TG_OP = 'INSERT' or (new.sortnum != old.sortnum or new.parent !=
old.parent) then
        perform recalc_sortnumpath(new.id);
    end if;
    return new;
end;

...because 'old' doesn't exist and the latter argument of the 'or'
gets evaluated despite the TG_OP being 'INSERT'.  According to the
docs I should change that line to:

if (select case when TG_OP = 'UPDATE' then (new.sortnum != old.sortnum
or new.parent != old.parent) else 't' end) then

...because the case should force it to only evaluate 'old' when TG_OP
=  'UPDATE' and otherwise ('INSERT') skip through to 't'.  But this
causes the same error on insert.  I suspect it's because the select
query gets parameterized and at that point the 'old' is missing,
before the case even gets to be parsed.  How do I get around this
without having two 'perform' statements?  Is there no short-circuit
option in plpgsql?

Thanks,
Kev

Kev <kevinjamesfield@gmail.com> writes:
> ...because the case should force it to only evaluate 'old' when TG_OP
> =  'UPDATE' and otherwise ('INSERT') skip through to 't'.  But this
> causes the same error on insert.  I suspect it's because the select
> query gets parameterized and at that point the 'old' is missing,
> before the case even gets to be parsed.

Got it in one.

> How do I get around this
> without having two 'perform' statements?

What you need is two nested IF statements.  The PERFORM in your example
is not relevant to the problem.

            regards, tom lane

> Kev <kevinjamesfield@gmail.com> writes:
> > ...because the case should force it to only evaluate 'old' when
> > TG_OP
> > =  'UPDATE' and otherwise ('INSERT') skip through to 't'.  But this
> > causes the same error on insert.  I suspect it's because the select
> > query gets parameterized and at that point the 'old' is missing,
> > before the case even gets to be parsed.
>
> Got it in one.

Thanks.  Shouldn't there be some way around this then?

> > How do I get around this
> > without having two 'perform' statements?
>
> What you need is two nested IF statements.  The PERFORM in your
> example
> is not relevant to the problem.
>
>             regards, tom lane

Well, sure, in one sense, but I am actually trying to make it look
neater.  Unless I'm missing something (quite possible...) the two
nested IF statements end up having two PERFORM statements:

if TG_OP = 'INSERT' then
    perform recalc_sortnumpath(new.id);
else
    if (new.sortnum != old.sortnum or new.parent != old.parent) then
        perform recalc_sortnumpath(new.id);
    end if;
end if;

...is there some way to boil this down using nested IF statements that
only has one PERFORM?  (I mean, besides inverting it and having three
return statements and one perform.)

Thanks,
Kev