2

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!

Improve this question
3
  • What's the function's language? Commented Feb 18, 2010 at 13:51
  • It's odd that you accepted the answer to use IMMUTABLE yet you say in a comment that your function "selects a value from another table and it could be changed for that particular id". I hope you've not marked that function IMMUTABLE since Postgres does not say how long it caches an immutable function's result. Commented Mar 3, 2010 at 11:29
  • Actually after a discussion in the comments section we agreed on STABLE and since it worked out fine for me I choose the corresponding answer. I guess that the author could have changed his answer to mirror the discussion, but that isn't that important at least not for me. Commented Mar 5, 2010 at 14:32

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

What happens if the result changes for the same arguments?
@stefita: you shouldn't mark such functions as IMMUTABLE. postgresql.org/docs/current/static/xfunc-volatility.html
@stefita: You mean like clock_timestamp() ? Then you declare it VOLATILE and Postgres will invoke it every time it sees it. What else could it possibly do? But your example looks like function(id) returns the same thing but you need it to select 3 different fields
@Adrian Pronk: the result of the function can change, since it selects a value from another table and it could be changed for that particular id.
@stefita: you should declare it as STABLE then. Within a query, the table state (as visible by the function) cannot change.
|
2

use a subquery :

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

Comments

2

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.