Re: right way of using case-expressions in plpgsql functions

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Sunday, October 15, 2023, Victor Dobrovolsky <booby(dot)stager(at)gmail(dot)com>
> wrote:
>> select (case when px is not null then pf = px
>> else pf is not distinct from py
>> end);

> Every single time this function is called “px is not null” will be
> evaluated and then one of the two branches will be evaluated. Nothing the
> optimizer does will change that. The planner for the function internals
> does not know whether px will or will not be null on any given invocation.

Not necessarily --- I think the SQL-language function will get inlined
and then there would be opportunity for const-simplification if a
known value is available for px in the outer function.

At least in the px-not-null case, having "pf = px" rather than an
impenetrable CASE condition will probably be enough better for
optimization that the plancache would never choose to switch to a
generic plan. However, that might not be true for the other case,
since we aren't terribly smart about optimizing NOT DISTINCT conditions.
So the performance you get might well vary depending on which case
occurs more often during the first few query runs.

On the whole though, the entire question seems like solving the wrong
problem. If you have to resort to this kind of thing to get your
answers, it's past time to rethink your data representation.

regards, tom lane