final
     query is executed to completion, and each row it
     outputs is returned as an element of the result set.
    </para>

    <para>
     This feature is normally used when calling the function in the <literal>FROM</literal>
     clause.  In this case each row returned by the function becomes
     a row of the table seen by the query.  For example, assume that
     table <literal>foo</literal> has the same contents as above, and we say:

<programlisting>
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;
</programlisting>

     Then we would get:
<screen>
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)
</screen>
    </para>

    <para>
     It is also possible to return multiple rows with the columns defined by
     output parameters, like this:

<programlisting>
CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)
</programlisting>

     The key point here is that you must write <literal>RETURNS SETOF record</literal>
     to indicate that the function returns multiple rows instead of just one.
     If there is only one output parameter, write that parameter's type
     instead of <type>record</type>.
    </para>

    <para>
     It is frequently useful to construct a query's result by invoking a
     set-returning function multiple times, with the parameters for each
     invocation coming from successive rows of a table or subquery.  The
     preferred way to do this is to use the <literal>LATERAL</literal> key word,
     which is described in <xref linkend="queries-lateral"/>.
     Here is an example using a set-returning function to enumerate
     elements of a tree structure:

<screen>
SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;

SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)
</screen>

     This example does not do anything that we couldn't have done with a
     simple join, but in more complex calculations the option to put
     some of the work into a function can be quite convenient.
    </para>

    <para>
     Functions returning sets can also be called in the select list
     of a query.  For each row that the query
     generates by itself, the set-returning function is invoked, and an output
     row is generated for each element of the function's result set.
     The previous example could also be done with queries like
     these:

<screen>
SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)
</screen>

     In the last <command>SELECT</command>,
     notice that no output row appears for <literal>Child2</literal>, <literal>Child3</literal>, etc.
     This happens because <function>listchildren</function> returns an empty set
     for those arguments, so no result rows are generated.  This is the same
     behavior as we got from an inner join to the function result when using