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

What is the difference between:

select foo();
and
select * from foo();

Foo is defined as:

CREATE OR REPLACE FUNCTION foo()
  RETURNS SETOF integer AS
    'SELECT column from foo_table;'
  LANGUAGE 'sql' STABLE;

Explain shows difference...

explain select * from foo()
"Function Scan on foo  (cost=0.00..1.25 rows=1000 width=4)"

Explain select foo();
"Result  (cost=0.00..0.00 rows=1 width=0)"

They both return the same results..yet yield different plans...and different speeds when using 'real' data.

Why come?

Jeff Amiel <becauseimjeff@yahoo.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