PostgreSQL Conditional selects

Question

I have written a recursive function and depending on the output I need to select different fields. My question is now, how can I do this multiple times without having to call the function more then once? What I'm doing right now is just using the CASE WHEN... condition and checking every time what the functions return. (This is only a pseudo code and doesn't do anything real, it's just for understanding)

SELECT
id,
(CASE WHEN (function(id) > 0)
    THEN field1
    ELSE field2
END) as value1,
(CASE WHEN (function(id) > 0)
    THEN field3
    ELSE field4
END) as value2,
(CASE WHEN (function(id) > 0)
    THEN field5
    ELSE field6
END) as value3
FROM table1
...

How can I optimize this query and call the function only once? Thanks in advance!

Answer 1

use a subquery :

SELECT foo, bar, result
FROM (
SELECT ..., function(id) AS result
....
) as tmp

Answer 2

If the function is declared IMMUTABLE, it is safe to call it many times, as it will not be reevaluated.

From the docs:

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

Answer 3

You may be able to use some funky tuple thing like:

SELECT id,
CASE WHEN function(id) > 0
    THEN (field1, field3, field5)
    ELSE (field2, field4, field6)
END as (value1, value2, value3)

but I have no experience with this syntax