Re: Evaluate only one CASE WHEN in a select

On Apr 12, 4:30 pm, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> "dcrespo" <dcre(dot)(dot)(dot)(at)gmail(dot)com> writes:
> > They are exactly the same, that's why I want to evaluate it only once
> > and, depending on it, put the corresponding value into two different
> > fields that must be returned, instead of evaluating once for each
> > field. Any insight?
>
> There's no solution that wouldn't cost you more than double evaluation,
> for such a simple expression.
>
> The general solution is to use two levels of SELECT:
>
> select ..., x, x, ...
> from (select ..., big-expr as x, ... from ... offset 0) ss;
>
> You need the "offset 0" (which is otherwise a no-op) to prevent the
> planner from folding the two selects into a single level and ending up
> with two copies of big-expr anyway. The runtime overhead associated
> with the extra plan level is about going to eat up whatever you might
> save in this example, though with a seriously expensive expression
> (for instance, a function that does some fairly expensive SELECT itself)
> you might find it worth doing.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
Thank you, Tom.

In your example, "x,x" seems to be the same value. I don't want that
exactly. Here is another example:

SELECT
CASE WHEN is_odd(t.number) THEN 'Update' ELSE 'Insert' END AS
action,
CASE WHEN is_odd(t.number) THEN t.number ELSE NULL END AS number,
FROM ... ;

As you can see, is_odd function (only a function example) is being run
twice with exactly the same parameters to put a value into action
field and into number field. Since it's the same function call, I want
it to be run only once and put the above values into their
corresponding fields. My actual function is not that expensive, but
has to be run for every retrieved row, obviously. Does this change
your explanation?

Thank you

Daniel