Re: CASE WHEN idiomatic for functions with side-effect?

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Jan 12, 2021 at 1:14 AM Joel Jacobson <joel@xxxxxxxxxxxx> wrote:

Is it idiomatic and safe to use

SELECT

  CASE boolean_expression WHEN TRUE THEN function_with_side_effects() END

As long as function_with_side_effects() is defined volatile it is forced to be executed at runtime, once per row.  That situation is always protected by the case _expression_.

"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 PL/pgSQL functions, for example, using an IF-THEN-ELSE statement to protect a risky computation is much safer than just nesting it in a CASE _expression_."

The affected real code: https://github.com/truthly/uniphant/blob/rls/FUNCTIONS/api/verify_assertion.sql

The relevant function takes in a column argument - it is thus impossible for the planner to evaluate the _expression_.  And, as above, the planner respects the "volatile" attribute of functions.

David J.