Re: func() & select func()

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
>> Currently we do not consider the presence of noncachable functions as
>> a reason that prevents reducing a subplan to an initplan. I thought
>> about it but didn't like the performance penalty. It seems to me that
>> it's debatable which is the correct semantics, anyway. Arguably an

> Shouldn't functions be evaluated once(per the combination of parameters)
> at the time when a query started ?

I don't think I want to buy into guaranteeing that, either. In the
first place, that makes it impossible to get a random sampling of your
data by methods like
SELECT * FROM foo WHERE random() < 0.01;
Now admittedly this is a little bit flaky (not least because you'd get
inconsistent results if you tried to use a cursor to scan the output
multiple times) but I think it's useful enough to not want to break it.
Especially not when I just recently put a few hours into making the
optimizer treat this case correctly ;-)

In the second place, to guarantee evaluate-once behavior with a
noncooperative function, you'd have to actually maintain a cache of
function parameter sets and result values and consult it before making
a function call. That we certainly don't want to do. So I think the
existing distinction between cachable and noncachable functions is
appropriate. The question is whether the presence of noncachable
functions should propagate out to cause the whole containing SELECT
to be treated as noncachable.

I think you could probably argue that either way, and could invent
examples favoring either choice. Without a compelling reason to change
the behavior, I'm inclined to leave it as is.

regards, tom lane