How to avoid multiple function evals with the (func()).* syntax in a query?

You can wrap it up in a subquery but that’s not guaranteed safe without the OFFSET 0 hack. In 9.3, use LATERAL. The problem is caused by the parser effectively macro-expanding * into a column list.

Workaround

Where:

SELECT (my_func(x)).* FROM some_table;

will evaluate my_func n times for n result columns from the function, this formulation:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table
) sub;

generally will not, and tends not to add an additional scan at runtime. To guarantee that multiple evaluation won’t be performed you can use the OFFSET 0 hack or abuse PostgreSQL’s failure to optimise across CTE boundaries:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

or:

WITH tmp(mf) AS (
    SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;

In PostgreSQL 9.3 you can use LATERAL to get a saner behaviour:

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;

LEFT JOIN LATERAL ... ON true retains all rows like the original query, even if the function call returns no row.

Demo

Create a function that isn’t inlineable as a demonstration:

CREATE OR REPLACE FUNCTION my_func(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
BEGIN
    RAISE NOTICE 'my_func(%)',$1;
    RETURN QUERY SELECT $1, $1, $1;
END;
$$ LANGUAGE plpgsql;

and a table of dummy data:

CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x;

then try the above versions. You’ll see that the first raises three notices per invocation; the latter only raise one.

Why?

Good question. It’s horrible.

It looks like:

(func(x)).*

is expanded as:

(my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l

in parsing, according to a look at debug_print_parse, debug_print_rewritten and debug_print_plan. The (trimmed) parse tree looks like this:

   :targetList (
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 1 
         :resulttype 23 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 1 
      :resname i 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 2 
         :resulttype 20 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 2 
      :resname j 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 3 
         :...
         }
      :resno 3 
      :resname k 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 4 
          ...
         }
      :resno 4 
      :resname l 
       ...
      }
   )

So basically, we’re using a dumb parser hack to expand wildcards by cloning nodes.