Re: FUNCTION returing multiple rows

Brett Schwarz <schwarz@PacketVideo.COM> writes:
> However, when I look at the programmers guide (Ch. 4), it almost leads me to
> believe otherwise:

> The following more interesting example takes a single argument of type EMP,
> and retrieves multiple results: 
> select function hobbies (EMP) returns set of HOBBIES
>     as 'select (HOBBIES.all) from HOBBIES
>         where $1.name = HOBBIES.person'
>     language 'sql';

This bit of the docs seems not to have been updated since PostQuel days.
The ".all" syntax is definitely out of date --- ".*" is the SQL
equivalent.  There is a descendant of this example in the current
regression tests:

CREATE FUNCTION hobbies(person)  RETURNS setof hobbies_r   AS 'select * from hobbies_r where person = $1.name'
LANGUAGE'sql';
 

(Note that `person' is being used in two ways here, first as a datatype
and then as a field name --- not the least confusing example I could
have thought of...)

> Also, is there any type of function return multiple rows (plpgsql, pltcl,
> etc)?

In 7.0 and prior releases, *only* SQL functions can return multiple
rows.  The 7.1 function manager removes that hardwired assumption,
but in 7.1 the plpgsql, pltcl, plperl function language handlers don't
take advantage of the capability --- so the current state of affairs
is that you can do it in SQL or C functions only.  Hopefully this will
be improved in future releases.
        regards, tom lane