diff options
Diffstat (limited to 'doc/src/sgml/syntax.sgml')
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 49 |
1 files changed, 49 insertions, 0 deletions
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index d09c808a048..2e5104526a8 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -2336,6 +2336,55 @@ SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; example, it would be better to sidestep the problem by writing <literal>y > 1.5*x</> instead.) </para> + + <para> + <literal>CASE</> is not a cure-all for such issues, however. + One limitation of the technique illustrated above is that it does not + prevent early evaluation of constant subexpressions. + As described in <xref linkend="xfunc-volatility">, functions and + operators marked <literal>IMMUTABLE</literal> can be evaluated when + the query is planned rather than when it is executed. Thus for example +<programlisting> +SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab; +</programlisting> + is likely to result in a division-by-zero failure due to the planner + trying to simplify the constant subexpression, + even if every row in the table has <literal>x > 0</> so that the + <literal>ELSE</> arm would never be entered at run time. + </para> + + <para> + While that particular example might seem silly, related cases that don't + obviously involve constants can occur in queries executed within + functions, since the values of function arguments and local variables + can be inserted into queries as constants for planning purposes. + Within <application>PL/pgSQL</> functions, for example, using an + <literal>IF</>-<literal>THEN</>-<literal>ELSE</> statement to protect + a risky computation is much safer than just nesting it in a + <literal>CASE</> expression. + </para> + + <para> + Another limitation of the same kind is that a <literal>CASE</> cannot + prevent evaluation of an aggregate expression contained within it, + because aggregate expressions are computed before other + expressions in a <literal>SELECT</> list or <literal>HAVING</> clause + are considered. For example, the following query can cause a + division-by-zero error despite seemingly having protected against it: +<programlisting> +SELECT CASE WHEN min(employees) > 0 + THEN avg(expenses / employees) + END + FROM departments; +</programlisting> + The <function>min()</> and <function>avg()</> aggregates are computed + concurrently over all the input rows, so if any row + has <structfield>employees</> equal to zero, the division-by-zero error + will occur before there is any opportunity to test the result of + <function>min()</>. Instead, use a <literal>WHERE</> + clause to prevent problematic input rows from + reaching an aggregate function in the first place. + </para> </sect2> </sect1> |