"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