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

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thu, Feb 18, 2016 at 6:57 AM, <harry(dot)townsend(at)eflowglobal(dot)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