Re: BUG #13973: Constants resolved in then/else clauses

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 18, 2016 at 6:57 AM, <harry.townsend@eflowglobal.com> wrote:
>> I attempted to create a safety check in a query using a "case when"
>> statement such that if the condition evaluated to false, it would return (1
>> / 0) in order to nullify the entire transaction.

> ​So, there is a note in the documentation that exactly addresses what you
> are trying to do....​
> http://www.postgresql.org/docs/current/static/functions-conditional.html

> ​"""
> ​As described in Section 4.2.14, there are various situations in which
> subexpressions of an expression are evaluated at different times, so that
> the principle that "CASE evaluates only necessary subexpressions" is not
> ironclad.* For example a constant 1/0 subexpression will usually result in
> a division-by-zero failure* at planning time, even if it's within a CASE
> arm that would never be entered at run time.
> """ (emphasis mine)

Yeah.  What you need to do is ensure that the failure-causing thing
doesn't look like a constant subexpression.  I'd suggest a more useful
approach is

create function fail() returns int as
$$begin raise exception ...; end$$ language plpgsql volatile;

 .... CASE WHEN <test condition> THEN 0 ELSE fail() END ...

The "volatile" marker on the function teaches the planner that
the function has side-effects (viz, an exception) and so must
not be speculatively evaluated.  This'd also have the advantage
of producing a much more useful error message (you might wanna
consider adding parameters to the function, such as text to go
into the error message).

            regards, tom lane