Re: function returning setof..select versus select * from

Jeff Amiel <becauseimjeff(at)yahoo(dot)com> writes:
> What is the difference between:
> select foo();
> and
> select * from foo();

They're implemented differently, partly for legacy or lack-of-round-tuit
reasons, and partly because different PLs prefer different strategies
for returning sets.

The first form only works for functions that are able to return one row
at a time, ie, suspend execution of a SRF until called again. I think
currently that is only true of SQL-language and some C functions.
The outer SELECT just returns the rows one at a time as they're returned
by the function.

In the second form the function is immediately executed to completion
and all the result rows are stuffed in a tuplestore. The surrounding
query then runs and draws the rows from the tuplestore. This is
particularly convenient for PLs like plpgsql, which return set results
as tuplestores in the first place, but we'll create a tuplestore anyway
if the function wants to return the rows one at a time.

regards, tom lane