plpgsql and logical expression evaluation

On Tue, Apr 22, 2008 at 02:41:50AM -0700, wstrzalka wrote:

One of the annoying things in plpgsql is logical expression
evaluation.

In most (all??) languages I know, logical expression like:

if ( [A_true_expression] or [B_false_expression] ) then

will stop evaluating when the A expression will be evaluated as a
TRUE.

I guess you should complain to the SQL design comittee, SQL is a
declarative language not a procedural language and the order of the
expressions in AND/OR is not important to the evaluation.

It don't work for insert as the part designed for UPDATE will be
evaluated, while there is no OLD for an insert operation.
So the code looks like that:

IF (TG_OP = 'INSERT') THEN
-- DO SOMETHING
ELSIF (TG_OP = 'UPDATE' AND NEW.status <> OLD.status) THEN
-- DO THE SAME AS ABOVE
END IF;

You could use a flag variable.

Is there any reason for that like side effects (please give me any
example?) or it's just not yet done optimization?

The spec explicitly says it doesn't matter and it removes the
possibility of optimisation (in many queries it is important that the
optimiser can execute conditions in any order it likes).

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout escribi�:

On Tue, Apr 22, 2008 at 02:41:50AM -0700, wstrzalka wrote:

One of the annoying things in plpgsql is logical expression
evaluation.

In most (all??) languages I know, logical expression like:

if ( [A_true_expression] or [B_false_expression] ) then

will stop evaluating when the A expression will be evaluated as a
TRUE.

I guess you should complain to the SQL design comittee, SQL is a
declarative language not a procedural language and the order of the
expressions in AND/OR is not important to the evaluation.

While I agree with that in principle, we should by now start getting
used to the idea that PL/pgSQL is a different language from SQL. For
example, SELECT INTO are totally different in both languages. So are
BEGIN and END, and then there are loads of procedural constructs not
found on SQL at all.

I think this business of non-shortcircuiting boolean operators is just
an artifact of the fact that PL/pgSQL hands off expression to the SQL
engine for evaluation.

Perhaps SQL/PSM is designed differently (I haven't checked), or PL/SQL
words differently on Oracle. If either of these cases is true, we will
need to attack the problem sooner or later.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

I think this business of non-shortcircuiting boolean operators is just
an artifact of the fact that PL/pgSQL hands off expression to the SQL
engine for evaluation.

The complainant is not actually complaining about non-shortcircuiting
boolean operators --- he thinks he is, but he's 100% mistaken. The
reason he's got a problem in the given example is that plpgsql has to
provide parameter values for every parameter in the whole expression
before it ships it off to the main engine. ExecEvalAnd actually *does*
know about short-circuiting, but it doesn't help because control never
gets that far.

Even if we rejiggered things so that supplying parameter values could be
done lazily, there's the little problem that we can't even parse the
expression without knowing the types of the parameters. The correct
analogy for what the OP tried to do is writing in C

if (x == 0 || no_such_var == 0)

and expecting the "undefined variable no_such_var" failure not to be
reported if x is zero. Since it's happening at compile time, that's
not going to happen.

regards, tom lane

On 23 Kwi, 16:32, t...@sss.pgh.pa.us (Tom Lane) wrote:

Alvaro Herrera <alvhe...@commandprompt.com> writes:

I think this business of non-shortcircuiting boolean operators is just
an artifact of the fact that PL/pgSQL hands off expression to the SQL
engine for evaluation.

The complainant is not actually complaining about non-shortcircuiting
boolean operators --- he thinks he is, but he's 100% mistaken. The
reason he's got a problem in the given example is that plpgsql has to
provide parameter values for every parameter in the whole expression
before it ships it off to the main engine. ExecEvalAnd actually *does*
know about short-circuiting, but it doesn't help because control never
gets that far.

Even if we rejiggered things so that supplying parameter values could be
done lazily, there's the little problem that we can't even parse the
expression without knowing the types of the parameters. The correct
analogy for what the OP tried to do is writing in C

if (x == 0 || no_such_var == 0)

and expecting the "undefined variable no_such_var" failure not to be
reported if x is zero. Since it's happening at compile time, that's
not going to happen.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Yes. You are right. I didn't realized what my complain was about.
But the 'no_such_var' example is great and explains what I was trying
to do, and why it doesn't work.

So - does it mean that the whole IF-ELSE-ENDIF is not parsed at once -
but lazy-parsed when the control reaches it, while the IF condition is
parsed as a single expression and therefore I get error in this case?

Thanks a lot.

regards
wojtek

wstrzalka <wstrzalka@gmail.com> writes:

So - does it mean that the whole IF-ELSE-ENDIF is not parsed at once -
but lazy-parsed when the control reaches it, while the IF condition is
parsed as a single expression and therefore I get error in this case?

Right, for a suitable definition of "parsed". There is some trivial
syntax checking that happens early (eg, you'll see complaints for
mismatched parentheses) but actually trying to determine the meaning
of an expression happens when control first gets there.

regards, tom lane