Attached is a patch implementing RETURN QUERY, per earlier discussion,
and based on a patch from Pavel Stehule. Like RETURN NEXT, RETURN QUERY
doesn't immediately return from the function, allowing RETURN NEXT and
RETURN QUERY to be intermixed in a single function.
Barring any objections, I'll apply this tomorrow.
-Neil
Index: doc/src/sgml/plpgsql.sgml
===
RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.115
diff -p -c -r1.115 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 16 Jul 2007 17:01:10 - 1.115
--- doc/src/sgml/plpgsql.sgml 24 Jul 2007 06:40:47 -
***
*** 135,141
PL/pgSQL functions can also be declared to return
a set, or table, of any data type they can return a single
instance of. Such a function generates its output by executing
! RETURN NEXT for each desired element of the result set.
--- 135,143
PL/pgSQL functions can also be declared to return
a set, or table, of any data type they can return a single
instance of. Such a function generates its output by executing
! RETURN NEXT for each desired element of the result
! set, or by using RETURN QUERY to output the result of
! executing a query.
*** RETURN expression
! RETURN NEXT
RETURN NEXT expression;
When a PL/pgSQL function is declared to return
SETOF sometype, the procedure
to follow is slightly different. In that case, the individual
! items to return are specified in RETURN NEXT
! commands, and then a final RETURN command
! with no argument is used to indicate that the function has
! finished executing. RETURN NEXT can be used
! with both scalar and composite data types; with a composite result
! type, an entire table of results will be returned.
! RETURN NEXT does not actually return from the
! function — it simply saves away the value of the expression.
! Execution then continues with the next statement in
! the PL/pgSQL function. As successive
! RETURN NEXT commands are executed, the result
! set is built up. A final RETURN, which should
! have no argument, causes control to exit the function (or you can
! just let control reach the end of the function).
If you declared the function with output parameters, write just
RETURN NEXT with no expression. On each
! execution, the current values
! of the output parameter variable(s) will be saved for eventual return
! as a row of the result.
! Note that you must declare the function as returning
! SETOF record when there are
! multiple output parameters, or
! SETOF sometype when there is
! just one output parameter of type sometype, in
! order to create a set-returning function with output parameters.
! Functions that use RETURN NEXT should be
! called in the following fashion:
SELECT * FROM some_func();
--- 1351,1419
! RETURN NEXT and RETURN QUERY
!
! RETURN NEXT
! in PL/PgSQL
!
!
! RETURN QUERY
! in PL/PgSQL
!
RETURN NEXT expression;
+ RETURN QUERY query;
When a PL/pgSQL function is declared to return
SETOF sometype, the procedure
to follow is slightly different. In that case, the individual
! items to return are specified by a sequence of RETURN
! NEXT or RETURN QUERY commands, and
! then a final RETURN command with no argument
! is used to indicate that the function has finished executing.
! RETURN NEXT can be used with both scalar and
! composite data types; with a composite result type, an entire
! table of results will be returned.
! RETURN QUERY appends the results of executing
! a query to the function's result set. RETURN
! NEXT and RETURN QUERY can be freely
! intermixed in a single set-returning function, in which case
! their results will be concatenated.
! RETURN NEXT and RETURN
! QUERY do not actually return from the function —
! they simply append zero or more rows to the function's result
! set. Execution then continues with the next statement in the
! PL/pgSQL function. As successive
! RETURN NEXT or RETURN
! QUERY commands are executed, the result set is built
! up. A final RETURN, which should have no
! argument, causes control to exit the function (or you can just
! let control reach the end of the function).
If you declared the function with output parameters, write just
RETURN NEXT with no expression. On each
! execution, the current