Aaron Swartz <me@aaronsw.com> writes:
> The function we're running returns an array, and it is non-trivial to
> compute. We do a simple query with it like "SELECT * FROM
> tablename WHERE id = any(foo(21))").
> When the function is STABLE (or VOLATILE) the function is run
> to generate the array every time. If the function is IMMUTABLE,
> the array is computed only once for this query, as we'd expect,
> and the query is fast.
Oh, you are misunderstanding the point of IMMUTABLE/STABLE.
STABLE essentially gives the planner permission to use the function
in an indexscan qualification. It does *not* cause any caching of
the function result in other contexts, which is what you seem to be
wishing would happen.
IMMUTABLE/STABLE/VOLATILE are promises from you to the system about
the behavior of the function, not promises from the system about
how it will choose to evaluate the function.
What I'd suggest is recasting the function to return a SETOF result
instead of an array, and then writing
SELECT * FROM tablename WHERE id IN (select * from foo(21))
This should get you a plan that will work reasonably well for you.
regards, tom lane