I've complained many times that
select (f()).*;
will execute f() once for each returned field of f() since the server
essentially expands that into:
select f().a, f().b;
try it yourself, see:
create function f(a out text, b out text) returns record as $$
begin
perform pg_sleep(1);
a := 'a'; b := 'b'; end;
$$ language plpgsql immutable;
If f returns a,b etc. This is true if the function f() is marked
stable or immutable. That it does this for immutable functions is
pretty awful but it's the stable case that I find much more
interesting -- most non-trivial functions that read from the database
are stable. Shouldn't the server be able to detect that function only
needs to be run once? By the way, this isn't just happening with
function calls. I've noticed the same behavior in queries like this:
create view v as
select
(select foo from foo where ...) as foo_1,
(select foo from foo where ...) as foo_2,
from complicated_query;
that when you query from v, you can sometimes see exploding subplans
such that when you pull a field from foo_1, it reruns the lookup on
foo.
So my question is this:
Can stable functions and other similar query expressions be optimized
so that they are not repeat evaluated like that without breaking
anything?
merlin