Call a set-returning function with an array argument multiple times

In Postgres 9.3 or later, it's typically best to use LEFT JOIN LATERAL ... ON true:

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

If the function foo() can return no rows, that's the safe form as it preserves all rows to the left of the join, even when no row is returned to the right.

Else, or if you want to exclude rows without result from the lateral join, use:

CROSS JOIN LATERAL foo(sub.arr)

or the shorthand:

, foo(sub.arr)

There is an explicit mention in the manual.

Craig's related answer (referenced by Daniel) is updated accordingly:

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

The function is called multiple times in this context not because of its inputs, but because of how func().* is implemented

This is explained at: How to avoid multiple function evals with the (func()).* syntax in an SQL query?

The following variant should work without multiple evals on all supported PostgreSQL versions (8.4 or newer):

WITH subq as (
  SELECT array_agg(data) as agg,
   dataid FROM datatable
   -- WHERE clause ?
   GROUP BY dataid)
SELECT foo(agg,dataid) FROM subq;