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 -0000	1.115
--- doc/src/sgml/plpgsql.sgml	24 Jul 2007 06:40:47 -0000
***************
*** 135,141 ****
       <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.
      </para>
  
      <para>
--- 135,143 ----
       <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
!      <command>RETURN NEXT</> for each desired element of the result
!      set, or by using <command>RETURN QUERY</> to output the result of
!      executing a query.
      </para>
  
      <para>
*************** RETURN <replaceable>expression</replacea
*** 1349,1400 ****
      </sect3>
  
      <sect3>
!      <title><command>RETURN NEXT</></title>
  
  <synopsis>
  RETURN NEXT <replaceable>expression</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.
       </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).
       </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.
       </para>
  
       <para>
!       Functions that use <command>RETURN NEXT</command> should be
!       called in the following fashion:
  
  <programlisting>
  SELECT * FROM some_func();
--- 1351,1419 ----
      </sect3>
  
      <sect3>
!      <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 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> 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.
       </para>
  
       <para>
!       Functions that use <command>RETURN NEXT</command> or
!       <command>RETURN QUERY</command> should be called in the
!       following fashion:
  
  <programlisting>
  SELECT * FROM some_func();
*************** SELECT * FROM some_func();
*** 1407,1412 ****
--- 1426,1432 ----
       <note>
        <para>
         The current implementation of <command>RETURN NEXT</command>
+        and <command>RETURN QUERY</command>
         for <application>PL/pgSQL</> stores the entire result set
         before returning from the function, as discussed above.  That
         means that if a <application>PL/pgSQL</> function produces a
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.104
diff -p -c -r1.104 gram.y
*** src/pl/plpgsql/src/gram.y	16 Jul 2007 17:01:10 -0000	1.104
--- src/pl/plpgsql/src/gram.y	24 Jul 2007 06:43:32 -0000
*************** static	PLpgSQL_stmt	*make_execsql_stmt(c
*** 32,37 ****
--- 32,38 ----
  static	PLpgSQL_stmt_fetch *read_fetch_direction(void);
  static	PLpgSQL_stmt	*make_return_stmt(int lineno);
  static	PLpgSQL_stmt	*make_return_next_stmt(int lineno);
+ static	PLpgSQL_stmt	*make_return_query_stmt(int lineno);
  static	void			 check_assignable(PLpgSQL_datum *datum);
  static	void			 read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
  										  bool *strict);
*************** static	void			 check_labels(const char *
*** 187,192 ****
--- 188,194 ----
  %token	K_NULL
  %token	K_OPEN
  %token	K_OR
+ %token	K_QUERY
  %token	K_PERFORM
  %token	K_ROW_COUNT
  %token	K_RAISE
*************** stmt_return		: K_RETURN lno
*** 1171,1176 ****
--- 1173,1182 ----
  						{
  							$$ = make_return_next_stmt($2);
  						}
+ 						else if (tok == K_QUERY)
+ 						{
+ 							$$ = make_return_query_stmt($2);
+ 						}
  						else
  						{
  							plpgsql_push_back_token(tok);
*************** make_return_stmt(int lineno)
*** 2104,2110 ****
  	if (plpgsql_curr_compile->fn_retset)
  	{
  		if (yylex() != ';')
! 			yyerror("RETURN cannot have a parameter in function returning set; use RETURN NEXT");
  	}
  	else if (plpgsql_curr_compile->out_param_varno >= 0)
  	{
--- 2110,2117 ----
  	if (plpgsql_curr_compile->fn_retset)
  	{
  		if (yylex() != ';')
! 			yyerror("RETURN cannot have a parameter in function "
! 					"returning set; use RETURN NEXT or RETURN QUERY");
  	}
  	else if (plpgsql_curr_compile->out_param_varno >= 0)
  	{
*************** make_return_next_stmt(int lineno)
*** 2200,2205 ****
--- 2207,2229 ----
  }
  
  
+ static PLpgSQL_stmt *
+ make_return_query_stmt(int lineno)
+ {
+ 	PLpgSQL_stmt_return_query *new;
+ 
+ 	if (!plpgsql_curr_compile->fn_retset)
+ 		yyerror("cannot use RETURN QUERY in a non-SETOF function");
+ 
+ 	new = palloc0(sizeof(PLpgSQL_stmt_return_query));
+ 	new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
+ 	new->lineno = lineno;
+ 	new->query = read_sql_construct(';', 0, ")", "", false, true, NULL);
+ 
+ 	return (PLpgSQL_stmt *) new;
+ }
+ 
+ 
  static void
  check_assignable(PLpgSQL_datum *datum)
  {
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.198
diff -p -c -r1.198 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c	15 Jul 2007 02:15:04 -0000	1.198
--- src/pl/plpgsql/src/pl_exec.c	18 Jul 2007 07:51:17 -0000
*************** static int exec_stmt_return(PLpgSQL_exec
*** 105,110 ****
--- 105,112 ----
  				 PLpgSQL_stmt_return *stmt);
  static int exec_stmt_return_next(PLpgSQL_execstate *estate,
  					  PLpgSQL_stmt_return_next *stmt);
+ static int exec_stmt_return_query(PLpgSQL_execstate *estate,
+ 					  PLpgSQL_stmt_return_query *stmt);
  static int exec_stmt_raise(PLpgSQL_execstate *estate,
  				PLpgSQL_stmt_raise *stmt);
  static int exec_stmt_execsql(PLpgSQL_execstate *estate,
*************** exec_stmt(PLpgSQL_execstate *estate, PLp
*** 1244,1249 ****
--- 1246,1255 ----
  			rc = exec_stmt_return_next(estate, (PLpgSQL_stmt_return_next *) stmt);
  			break;
  
+ 		case PLPGSQL_STMT_RETURN_QUERY:
+ 			rc = exec_stmt_return_query(estate, (PLpgSQL_stmt_return_query *) stmt);
+ 			break;
+ 
  		case PLPGSQL_STMT_RAISE:
  			rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt);
  			break;
*************** exec_stmt_return_next(PLpgSQL_execstate 
*** 2137,2142 ****
--- 2143,2201 ----
  	return PLPGSQL_RC_OK;
  }
  
+ /* ----------
+  * exec_stmt_return_query		Evaluate a query and add it to the
+  *								list of tuples returned by the current
+  *								SRF.
+  * ----------
+  */
+ static int
+ exec_stmt_return_query(PLpgSQL_execstate *estate,
+ 					   PLpgSQL_stmt_return_query *stmt)
+ {
+ 	Portal 		portal;
+ 
+ 	if (!estate->retisset)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_SYNTAX_ERROR),
+ 				 errmsg("cannot use RETURN QUERY in a non-SETOF function")));
+ 
+ 	if (estate->tuple_store == NULL)
+ 		exec_init_tuple_store(estate);
+ 
+ 	exec_run_select(estate, stmt->query, 0, &portal);
+ 
+ 	if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_DATATYPE_MISMATCH),
+ 				 errmsg("structure of query does not match function result type")));
+ 
+ 	while (true)
+ 	{
+ 		MemoryContext 	old_cxt;
+ 		int 			i;
+ 
+ 		SPI_cursor_fetch(portal, true, 50);
+ 		if (SPI_processed == 0)
+ 			break;
+ 
+ 		old_cxt = MemoryContextSwitchTo(estate->tuple_store_cxt);
+ 		for (i = 0; i < SPI_processed; i++)
+ 		{
+ 			HeapTuple tuple = SPI_tuptable->vals[i];
+ 			tuplestore_puttuple(estate->tuple_store, tuple);
+ 		}
+ 		MemoryContextSwitchTo(old_cxt);
+ 
+ 		SPI_freetuptable(SPI_tuptable);
+ 	}
+ 
+ 	SPI_freetuptable(SPI_tuptable);
+ 	SPI_cursor_close(portal);
+ 
+ 	return PLPGSQL_RC_OK;
+ }
+ 
  static void
  exec_init_tuple_store(PLpgSQL_execstate *estate)
  {
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.62
diff -p -c -r1.62 pl_funcs.c
*** src/pl/plpgsql/src/pl_funcs.c	20 Jul 2007 16:23:34 -0000	1.62
--- src/pl/plpgsql/src/pl_funcs.c	24 Jul 2007 06:42:39 -0000
*************** plpgsql_stmt_typename(PLpgSQL_stmt *stmt
*** 443,448 ****
--- 443,450 ----
  			return "RETURN";
  		case PLPGSQL_STMT_RETURN_NEXT:
  			return "RETURN NEXT";
+ 		case PLPGSQL_STMT_RETURN_QUERY:
+ 			return "RETURN QUERY";
  		case PLPGSQL_STMT_RAISE:
  			return "RAISE";
  		case PLPGSQL_STMT_EXECSQL:
*************** static void dump_fors(PLpgSQL_stmt_fors 
*** 484,489 ****
--- 486,492 ----
  static void dump_exit(PLpgSQL_stmt_exit *stmt);
  static void dump_return(PLpgSQL_stmt_return *stmt);
  static void dump_return_next(PLpgSQL_stmt_return_next *stmt);
+ static void dump_return_query(PLpgSQL_stmt_return_query *stmt);
  static void dump_raise(PLpgSQL_stmt_raise *stmt);
  static void dump_execsql(PLpgSQL_stmt_execsql *stmt);
  static void dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt);
*************** dump_stmt(PLpgSQL_stmt *stmt)
*** 542,547 ****
--- 545,553 ----
  		case PLPGSQL_STMT_RETURN_NEXT:
  			dump_return_next((PLpgSQL_stmt_return_next *) stmt);
  			break;
+ 		case PLPGSQL_STMT_RETURN_QUERY:
+ 			dump_return_query((PLpgSQL_stmt_return_query *) stmt);
+ 			break;
  		case PLPGSQL_STMT_RAISE:
  			dump_raise((PLpgSQL_stmt_raise *) stmt);
  			break;
*************** dump_return_next(PLpgSQL_stmt_return_nex
*** 879,884 ****
--- 885,899 ----
  }
  
  static void
+ dump_return_query(PLpgSQL_stmt_return_query *stmt)
+ {
+ 	dump_ind();
+ 	printf("RETURN QUERY ");
+ 	dump_expr(stmt->query);
+ 	printf("\n");
+ }
+ 
+ static void
  dump_raise(PLpgSQL_stmt_raise *stmt)
  {
  	ListCell   *lc;
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.90
diff -p -c -r1.90 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h	16 Jul 2007 17:01:11 -0000	1.90
--- src/pl/plpgsql/src/plpgsql.h	18 Jul 2007 07:43:38 -0000
*************** enum
*** 83,88 ****
--- 83,89 ----
  	PLPGSQL_STMT_EXIT,
  	PLPGSQL_STMT_RETURN,
  	PLPGSQL_STMT_RETURN_NEXT,
+ 	PLPGSQL_STMT_RETURN_QUERY,
  	PLPGSQL_STMT_RAISE,
  	PLPGSQL_STMT_EXECSQL,
  	PLPGSQL_STMT_DYNEXECUTE,
*************** typedef struct
*** 494,499 ****
--- 495,507 ----
  } PLpgSQL_stmt_return_next;
  
  typedef struct
+ {								/* RETURN QUERY statement */
+ 	int			cmd_type;
+ 	int			lineno;
+ 	PLpgSQL_expr *query;
+ } PLpgSQL_stmt_return_query;
+ 
+ typedef struct
  {								/* RAISE statement			*/
  	int			cmd_type;
  	int			lineno;
Index: src/pl/plpgsql/src/scan.l
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.57
diff -p -c -r1.57 scan.l
*** src/pl/plpgsql/src/scan.l	29 Apr 2007 01:21:09 -0000	1.57
--- src/pl/plpgsql/src/scan.l	18 Jul 2007 07:43:38 -0000
*************** declare			{ return K_DECLARE;			}
*** 124,131 ****
  default			{ return K_DEFAULT;			}
  diagnostics		{ return K_DIAGNOSTICS;		}
  else			{ return K_ELSE;			}
! elseif          { return K_ELSIF;           }
! elsif           { return K_ELSIF;           }
  end				{ return K_END;				}
  exception		{ return K_EXCEPTION;		}
  execute			{ return K_EXECUTE;			}
--- 124,131 ----
  default			{ return K_DEFAULT;			}
  diagnostics		{ return K_DIAGNOSTICS;		}
  else			{ return K_ELSE;			}
! elseif			{ return K_ELSIF;			}
! elsif			{ return K_ELSIF;			}
  end				{ return K_END;				}
  exception		{ return K_EXCEPTION;		}
  execute			{ return K_EXECUTE;			}
*************** null			{ return K_NULL;			}
*** 151,156 ****
--- 151,157 ----
  open			{ return K_OPEN;			}
  or				{ return K_OR;				}
  perform			{ return K_PERFORM;			}
+ query			{ return K_QUERY;			}
  raise			{ return K_RAISE;			}
  rename			{ return K_RENAME;			}
  result_oid		{ return K_RESULT_OID;		}
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.57
diff -p -c -r1.57 plpgsql.out
*** src/test/regress/expected/plpgsql.out	20 Jul 2007 16:38:38 -0000	1.57
--- src/test/regress/expected/plpgsql.out	24 Jul 2007 06:35:59 -0000
*************** NOTICE:  innerblock.param1 = 2
*** 3079,3081 ****
--- 3079,3130 ----
  (1 row)
  
  drop function pl_qual_names(int);
+ -- tests for RETURN QUERY
+ create function ret_query1(out int, out int) returns setof record as $$
+ begin
+     $1 := -1;
+     $2 := -2;
+     return next;
+     return query select x + 1, x * 10 from generate_series(0, 10) s (x);
+     return next;
+ end;
+ $$ language plpgsql;
+ select * from ret_query1();
+  column1 | column2 
+ ---------+---------
+       -1 |      -2
+        1 |       0
+        2 |      10
+        3 |      20
+        4 |      30
+        5 |      40
+        6 |      50
+        7 |      60
+        8 |      70
+        9 |      80
+       10 |      90
+       11 |     100
+       -1 |      -2
+ (13 rows)
+ 
+ create type record_type as (x text, y int, z boolean);
+ create or replace function ret_query2(lim int) returns setof record_type as $$
+ begin
+     return query select md5(s.x::text), s.x, s.x > 0
+                  from generate_series(-8, lim) s (x) where s.x % 2 = 0;
+ end;
+ $$ language plpgsql;
+ select * from ret_query2(8);
+                 x                 | y  | z 
+ ----------------------------------+----+---
+  a8d2ec85eaf98407310b72eb73dda247 | -8 | f
+  596a3d04481816330f07e4f97510c28f | -6 | f
+  0267aaf632e87a63288a08331f22c7c3 | -4 | f
+  5d7b9adcbe1c629ec722529dd12e5129 | -2 | f
+  cfcd208495d565ef66e7dff9f98764da |  0 | f
+  c81e728d9d4c2f636f067f89cc14862c |  2 | t
+  a87ff679a2f3e71d9181a67b7542122c |  4 | t
+  1679091c5a880faf6fb5e6087eb1b2dc |  6 | t
+  c9f0f895fb98ab9159f51fd0297e236d |  8 | t
+ (9 rows)
+ 
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.48
diff -p -c -r1.48 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql	16 Jul 2007 17:01:11 -0000	1.48
--- src/test/regress/sql/plpgsql.sql	24 Jul 2007 06:33:55 -0000
*************** $$ language plpgsql;
*** 2557,2559 ****
--- 2557,2583 ----
  select pl_qual_names(42);
  
  drop function pl_qual_names(int);
+ 
+ -- tests for RETURN QUERY
+ create function ret_query1(out int, out int) returns setof record as $$
+ begin
+     $1 := -1;
+     $2 := -2;
+     return next;
+     return query select x + 1, x * 10 from generate_series(0, 10) s (x);
+     return next;
+ end;
+ $$ language plpgsql;
+ 
+ select * from ret_query1();
+ 
+ create type record_type as (x text, y int, z boolean);
+ 
+ create or replace function ret_query2(lim int) returns setof record_type as $$
+ begin
+     return query select md5(s.x::text), s.x, s.x > 0
+                  from generate_series(-8, lim) s (x) where s.x % 2 = 0;
+ end;
+ $$ language plpgsql;
+ 
+ select * from ret_query2(8);
\ No newline at end of file
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to