Re: Repeat execution of stable expressions

hi,

> 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;


i ran into this regularly too. when f() is expensive then i try to rewrite the query so that the
function only get called once per row.

# explain analyze select (f()).*;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.51 rows=1 width=0) (actual time=2001.116..2001.117 rows=1 loops=1)
 Total runtime: 2001.123 ms

# explain analyze select f.* from f() as f;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Function Scan on f  (cost=0.25..0.26 rows=1 width=64) (actual time=1000.928..1000.928 rows=1 loops=1)
 Total runtime: 1000.937 ms

regards, jan