Re: unexpected stable function behavior

[Julius Tuskenis <julius@xxxxxxxx>]

Hello, Merlin

Thank you for your quick response.

2011.03.10 23:14, Merlin Moncure raÅÄ:

This is a huge problem with non trivial functions in the select list.
Pushing the result into and a subquery does NOT guarantee that the
inner result is materialized first.

From the postgresql documentation about STABLE functions: "This category allows the optimizer to optimize multiple calls of the function to a single call." I thought that this means that optimizer executes the function only for now parameter sets and stores results in some "cache" and use it if the parameters are already known. I realize this is very naive approach and most probably everything is much more complicated. I would appreciate if someone would explain the mechanism (or provide with some useful link).

   Try a CTE.

with foo as
(
  select yadda;
)
select func(foo.a), foo.* from foo;

I'm sorry, but I'm totally new to CTE. Would you please show me how should I use the stable function and where the parameters should be put to improve the behavior of the optimizer for my problem?

Thank you in advance

-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050