Implement RETURN QUERY for PL/PgSQL. This provides some convenient syntax

sugar for PL/PgSQL set-returning functions that want to return the result
of evaluating a query; it should also be more efficient than repeated
RETURN NEXT statements. Based on an earlier patch from Pavel Stehule.

1 files changed, 48 insertions, 29 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 24bcf52a4f7..70a0c4e6dcc 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.115 2007/07/16 17:01:10 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.116 2007/07/25 04:19:08 neilc Exp $ -->
 
 <chapter id="plpgsql"> 
   <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -135,7 +135,9 @@
      <application>PL/pgSQL</> functions can also be declared to return
      a <quote>set</>, or table, of any data type they can return a single
      instance of.  Such a function generates its output by executing
-     <literal>RETURN NEXT</> for each desired element of the result set.
+     <command>RETURN NEXT</> for each desired element of the result
+     set, or by using <command>RETURN QUERY</> to output the result of
+     evaluating a query.
     </para>
 
     <para>
@@ -1349,52 +1351,69 @@ RETURN <replaceable>expression</replaceable>;
     </sect3>
 
     <sect3>
-     <title><command>RETURN NEXT</></title>
+     <title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title>
+    <indexterm>
+     <primary>RETURN NEXT</primary>
+     <secondary>in PL/PgSQL</secondary>
+    </indexterm>
+    <indexterm>
+     <primary>RETURN QUERY</primary>
+     <secondary>in PL/PgSQL</secondary>
+    </indexterm>
 
 <synopsis>
 RETURN NEXT <replaceable>expression</replaceable>;
+RETURN QUERY <replaceable>query</replaceable>;
 </synopsis>
 
      <para>
       When a <application>PL/pgSQL</> function is declared to return
       <literal>SETOF <replaceable>sometype</></literal>, the procedure
       to follow is slightly different.  In that case, the individual
-      items to return are specified in <command>RETURN NEXT</command>
-      commands, and then a final <command>RETURN</command> command
-      with no argument is used to indicate that the function has
-      finished executing.  <command>RETURN NEXT</command> can be used
-      with both scalar and composite data types; with a composite result
-      type, an entire <quote>table</quote> of results will be returned.
+      items to return are specified by a sequence of <command>RETURN
+      NEXT</command> or <command>RETURN QUERY</command> commands, and
+      then a final <command>RETURN</command> command with no argument
+      is used to indicate that the function has finished executing.
+      <command>RETURN NEXT</command> can be used with both scalar and
+      composite data types; with a composite result type, an entire
+      <quote>table</quote> of results will be returned.
+      <command>RETURN QUERY</command> appends the results of executing
+      a query to the function's result set. <command>RETURN
+      NEXT</command> and <command>RETURN QUERY</command> can be freely
+      intermixed in a single set-returning function, in which case
+      their results will be concatenated.
      </para>
 
      <para>
-      <command>RETURN NEXT</command> does not actually return from the
-      function &mdash; it simply saves away the value of the expression.
-      Execution then continues with the next statement in
-      the <application>PL/pgSQL</> function.  As successive
-      <command>RETURN NEXT</command> commands are executed, the result
-      set is built up.  A final <command>RETURN</command>, which should
-      have no argument, causes control to exit the function (or you can
-      just let control reach the end of the function).
+      <command>RETURN NEXT</command> and <command>RETURN
+      QUERY</command> do not actually return from the function &mdash;
+      they simply append zero or more rows to the function's result
+      set.  Execution then continues with the next statement in the
+      <application>PL/pgSQL</> function.  As successive
+      <command>RETURN NEXT</command> or <command>RETURN
+      QUERY</command> commands are executed, the result set is built
+      up.  A final <command>RETURN</command>, which should have no
+      argument, causes control to exit the function (or you can just
+      let control reach the end of the function).
      </para>
 
      <para>
       If you declared the function with output parameters, write just
       <command>RETURN NEXT</command> 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
-      <literal>SETOF record</literal> when there are
-      multiple output parameters, or
-      <literal>SETOF <replaceable>sometype</></literal> when there is
-      just one output parameter of type <replaceable>sometype</>, in
-      order to create a set-returning function with output parameters.
+      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
+      <literal>SETOF record</literal> when there are multiple output
+      parameters, or <literal>SETOF <replaceable>sometype</></literal>
+      when there is just one output parameter of type
+      <replaceable>sometype</>, in order to create a set-returning
+      function with output parameters.
      </para>
 
      <para>
-      Functions that use <command>RETURN NEXT</command> should be
-      called in the following fashion:
+      Functions that use <command>RETURN NEXT</command> or
+      <command>RETURN QUERY</command> should be called in the
+      following fashion:
 
 <programlisting>
 SELECT * FROM some_func();
@@ -1407,7 +1426,7 @@ SELECT * FROM some_func();
      <note>
       <para>
        The current implementation of <command>RETURN NEXT</command>
-       for <application>PL/pgSQL</> stores the entire result set
+       and <command>RETURN QUERY</command> stores the entire result set
        before returning from the function, as discussed above.  That
        means that if a <application>PL/pgSQL</> function produces a
        very large result set, performance might be poor: data will be