CASE WHEN idiomatic for functions with side-effect?

From: "Joel Jacobson" <joel(at)compiler(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: CASE WHEN idiomatic for functions with side-effect?
Date: 2021-01-12 08:13:47
Message-ID: 6aab85ba-a97c-4394-ab80-6077bb14be2b@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is it idiomatic and safe to use

SELECT
CASE boolean_expression WHEN TRUE THEN function_with_side_effects() END
...

in a query to ensure the function_with_side_effects() is only
execute if boolean_expression is true?

function_with_side_effects() is known to be a normal function,
and not an aggregate function.

The documentation at

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

says the following:

"But this is safe:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;"

On the other hand, it also says:

"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."

Can I trust PostgreSQL not to execute function_with_side_effects() unless boolean_expression IS TRUE?

Or should I rewrite my code to use a PL/pgSQL function with IF-THEN-ELSE here instead?

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

Many thanks for clarifying.

/Joel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Голубева Яна 2021-01-12 08:20:59 Re: Define hash partition for certain column values
Previous Message James (王旭) 2021-01-12 03:05:22 Re: What to do with tablespaces when upgrading to pg13 from pg1X?