Thread: "and then" / "or else"

Hi list,
the logical operators "and" and "or" are commutative, i.e. there is no
"short-circuiting". Especially when doing PL/pgSQL development it would
sometimes be very handy to have this short circuiting. Unfortunately,
the trick from the docs (chapter 4.2.12) using "case ... then" does not
work inside an "if" statement (the "then" of the "case" is interpreted
as belonging to the "if" and thus leads to a syntax error).
Of course, I can sometimes use nested "if" statements, but that doesn't
always meet the logical requirements and leads to less readable code.
Some programming languages (e.g. Eiffel) know the operators "and then"
and "or else" which explicitly are "short-circuited". Would it be
possible to add these operators to PostgreSQL in general or at least to
the PL/pgSQL syntax?

Regards,
    Christian

--
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer

On Nov 17, 2007, at 3:53 , Christian Schröder wrote:

> Unfortunately, the trick from the docs (chapter 4.2.12) using
> "case ... then" does not work inside an "if" statement (the "then"
> of the "case" is interpreted as belonging to the "if" and thus
> leads to a syntax error).

I think if you use parentheses you can avoid the syntax error:

CREATE FUNCTION test_case_in_if
(in in_a boolean, in in_b boolean, in in_c boolean)
RETURNS text
STABLE
STRICT
LANGUAGE plpgsql AS $body$
begin
   if (CASE WHEN in_a then (in_b and in_c) else in_b end)
   then
     return 'first branch';
   else
     return 'second branch';
   end if;
END
$body$;

test=# select test_case_in_if(true, true, true);
test_case_in_if
-----------------
first branch
(1 row)

test=# select test_case_in_if(true, false, true);
test_case_in_if
-----------------
second branch
(1 row)

test=# select test_case_in_if(false, true, false);
test_case_in_if
-----------------
first branch
(1 row)

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:
> On Nov 17, 2007, at 3:53 , Christian Schröder wrote:
>
>> Unfortunately, the trick from the docs (chapter 4.2.12) using "case
>> ... then" does not work inside an "if" statement (the "then" of the
>> "case" is interpreted as belonging to the "if" and thus leads to a
>> syntax error).
>
> I think if you use parentheses you can avoid the syntax error:
Ah, I didn't know that parentheses are allowed here. (And I must admit I
didn't try.)
Nonetheless, I think it would improve readability to have an extra
operator for this.

Regards,
    Christian

--
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer