*** ./doc/src/sgml/plpgsql.sgml.orig	2007-04-29 03:21:08.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml	2007-05-06 10:00:58.000000000 +0200
***************
*** 1576,1584 ****
      <sect3>
       <title><command>RETURN</></title>
  
! <synopsis>
! RETURN <replaceable>expression</replaceable>;
! </synopsis>
  
       <para>
        <command>RETURN</command> with an expression terminates the
--- 1576,1589 ----
      <sect3>
       <title><command>RETURN</></title>
  
!     <itemizedlist>
!      <listitem>
!       <para><literal>RETURN <replaceable>expression</replaceable>;</></>
!      </listitem>
!      <listitem>
!       <para><literal>RETURN TABLE ( <replaceable>query</replaceable> );</></>
!      </listitem>
!     </itemizedlist>
  
       <para>
        <command>RETURN</command> with an expression terminates the
***************
*** 1603,1608 ****
--- 1608,1622 ----
       </para>
  
       <para>
+       If your function returns a set, you can use table expression. 
+       An <replaceable>query</replaceable> is evaluated and result set
+       is returned to the caller. You cannot mix <command>RETURN TABLE</command>
+       and <command>RETURN NEXT</command> statements in one function. 
+       Performance might be poor, if result set is very large. The reason
+       is described below.
+      </para>
+ 
+      <para>
        If you declared the function to return <type>void</type>, a
        <command>RETURN</command> statement can be used to exit the function
        early; but do not write an expression following
*** ./src/pl/plpgsql/src/gram.y.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y	2007-05-06 10:16:56.000000000 +0200
***************
*** 30,36 ****
  static	PLpgSQL_type	*read_datatype(int tok);
  static	PLpgSQL_stmt	*make_execsql_stmt(const char *sqlstart, int lineno);
  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	void			 check_assignable(PLpgSQL_datum *datum);
  static	void			 read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
--- 30,36 ----
  static	PLpgSQL_type	*read_datatype(int tok);
  static	PLpgSQL_stmt	*make_execsql_stmt(const char *sqlstart, int lineno);
  static	PLpgSQL_stmt_fetch *read_fetch_direction(void);
! static	PLpgSQL_stmt	*make_return_stmt(int lineno, bool is_tblexpr);
  static	PLpgSQL_stmt	*make_return_next_stmt(int lineno);
  static	void			 check_assignable(PLpgSQL_datum *datum);
  static	void			 read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
***************
*** 196,201 ****
--- 196,202 ----
  %token	K_REVERSE
  %token	K_SCROLL
  %token	K_STRICT
+ %token	K_TABLE
  %token	K_THEN
  %token	K_TO
  %token	K_TYPE
***************
*** 1185,1194 ****
  						{
  							$$ = make_return_next_stmt($2);
  						}
  						else
  						{
  							plpgsql_push_back_token(tok);
! 							$$ = make_return_stmt($2);
  						}
  					}
  				;
--- 1186,1200 ----
  						{
  							$$ = make_return_next_stmt($2);
  						}
+ 						else if (tok == K_TABLE)
+ 						{
+ 							$$ = make_return_stmt($2, true);
+ 							
+ 						}
  						else
  						{
  							plpgsql_push_back_token(tok);
! 							$$ = make_return_stmt($2, false);
  						}
  					}
  				;
***************
*** 2105,2111 ****
  
  
  static PLpgSQL_stmt *
! make_return_stmt(int lineno)
  {
  	PLpgSQL_stmt_return *new;
  
--- 2111,2117 ----
  
  
  static PLpgSQL_stmt *
! make_return_stmt(int lineno, bool is_tblexpr)
  {
  	PLpgSQL_stmt_return *new;
  
***************
*** 2114,2121 ****
  	new->lineno   = lineno;
  	new->expr	  = NULL;
  	new->retvarno = -1;
  
! 	if (plpgsql_curr_compile->fn_retset)
  	{
  		if (yylex() != ';')
  			yyerror("RETURN cannot have a parameter in function returning set; use RETURN NEXT");
--- 2120,2141 ----
  	new->lineno   = lineno;
  	new->expr	  = NULL;
  	new->retvarno = -1;
+ 	new->is_tblexpr = is_tblexpr;
+ 
+ 	if (is_tblexpr)
+ 	{
+ 		if (!plpgsql_curr_compile->fn_retset)
+ 			yyerror("table expression can be used only if function is SETOF or TABLE function");
  
! 		if (yylex() != '(')
! 			yyerror("expected '('");
! 
! 		new->expr = read_sql_construct(')', 0, ")", "", false, true, NULL);
! 		
! 		if (yylex() != ';')
! 			yyerror("expected ';'");
! 	}
! 	else if (plpgsql_curr_compile->fn_retset)
  	{
  		if (yylex() != ';')
  			yyerror("RETURN cannot have a parameter in function returning set; use RETURN NEXT");
*** ./src/pl/plpgsql/src/pl_comp.c.orig	2007-05-06 09:56:17.000000000 +0200
--- ./src/pl/plpgsql/src/pl_comp.c	2007-05-06 10:00:59.000000000 +0200
***************
*** 399,404 ****
--- 399,408 ----
  				PLpgSQL_variable *argvariable;
  				int			argitemtype;
  
+ 				/* skip table function columns */
+ 				if (argmode == PROARGMODE_TABLE)
+ 					continue;
+ 
  				/* Create $n name for variable */
  				snprintf(buf, sizeof(buf), "$%d", i + 1);
  
*** ./src/pl/plpgsql/src/pl_exec.c.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c	2007-05-06 10:00:59.000000000 +0200
***************
*** 175,180 ****
--- 175,181 ----
  static void exec_set_found(PLpgSQL_execstate *estate, bool state);
  static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
  static void free_var(PLpgSQL_var *var);
+ static void materialize_portal(PLpgSQL_execstate *estate, Portal portal);
  
  
  /* ----------
***************
*** 1871,1877 ****
--- 1872,1888 ----
  	 * the work will be done at the top level.
  	 */
  	if (estate->retisset)
+ 	{
+ 		if (stmt->is_tblexpr)
+ 		{
+ 			Portal 	portal;
+ 
+ 			exec_run_select(estate, stmt->expr, 0, &portal);
+ 			materialize_portal(estate, portal);
+ 		}
+ 
  		return PLPGSQL_RC_RETURN;
+ 	}
  
  	/* initialize for null result (possibly a tuple) */
  	estate->retval = (Datum) 0;
***************
*** 4984,4986 ****
--- 4995,5059 ----
  		var->freeval = false;
  	}
  }
+ 
+ 
+ /*
+  * Fetch all rows from portal and store its in list of tuples returned 
+  * by the current SRF.
+  */
+ static void
+ materialize_portal(PLpgSQL_execstate *estate, Portal portal)
+ {
+         SPITupleTable *tuptab;
+         int             n;
+         int             i;
+         TupleDesc       tupdesc;
+         int                     natts;
+         HeapTuple       tuple;
+ 
+         if (estate->tuple_store)
+                 elog(ERROR, "cannot mix return table and return next statement");
+ 
+         exec_init_tuple_store(estate);
+ 
+         tupdesc = estate->rettupdesc;
+         natts = tupdesc->natts;
+ 
+         SPI_cursor_fetch(portal, true, 10);
+         n = SPI_processed;
+         tuptab = SPI_tuptable;
+ 
+         if (!compatible_tupdesc(tupdesc, tuptab->tupdesc))
+                 ereport(ERROR,
+                                 (errcode(ERRCODE_DATATYPE_MISMATCH),
+                                 errmsg("incompatible TABLE expression in RETURN statement")));
+ 
+         while (n > 0)
+         {
+                 for (i = 0; i < n; i++)
+                 {
+                         tuple = tuptab->vals[i];
+ 
+                         if (HeapTupleIsValid(tuple))
+                         {
+                                 MemoryContext oldcxt;
+ 
+                                 oldcxt = MemoryContextSwitchTo(estate->tuple_store_cxt);
+                                 tuplestore_puttuple(estate->tuple_store, tuple);
+                                 MemoryContextSwitchTo(oldcxt);
+                         }
+                 }
+ 
+                 SPI_freetuptable(tuptab);
+ 
+                 /*
+                  * Fetch the next 50 tuples
+                  */
+                 SPI_cursor_fetch(portal, true, 50);
+                 n = SPI_processed;
+                 tuptab = SPI_tuptable;
+         }
+ 
+         SPI_freetuptable(tuptab);
+         SPI_cursor_close(portal);
+ }
*** ./src/pl/plpgsql/src/pl_funcs.c.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c	2007-05-06 10:00:59.000000000 +0200
***************
*** 857,862 ****
--- 857,864 ----
  {
  	dump_ind();
  	printf("RETURN ");
+ 	if (stmt->is_tblexpr)
+ 		printf("TABLE ");
  	if (stmt->retvarno >= 0)
  		printf("variable %d", stmt->retvarno);
  	else if (stmt->expr != NULL)
*** ./src/pl/plpgsql/src/plpgsql.h.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h	2007-05-06 10:00:59.000000000 +0200
***************
*** 483,488 ****
--- 483,489 ----
  	int			lineno;
  	PLpgSQL_expr *expr;
  	int			retvarno;
+ 	bool			is_tblexpr;
  } PLpgSQL_stmt_return;
  
  typedef struct
*** ./src/pl/plpgsql/src/scan.l.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/pl/plpgsql/src/scan.l	2007-05-06 10:00:59.000000000 +0200
***************
*** 159,164 ****
--- 159,165 ----
  row_count		{ return K_ROW_COUNT;		}
  scroll			{ return K_SCROLL;			}
  strict			{ return K_STRICT;	    	}
+ table			{ return K_TABLE;		}
  then			{ return K_THEN;			}
  to				{ return K_TO;				}
  type			{ return K_TYPE;			}
*** ./src/test/regress/expected/plpgsql.out.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out	2007-05-06 10:47:19.000000000 +0200
***************
*** 3051,3053 ****
--- 3051,3124 ----
  (3 rows)
  
  drop function sc_test();
+  
+ -- table functions test
+ create table tbl_test(
+   a int,
+   b int
+ );
+ insert into tbl_test select a, b from generate_series(1,5) a(a), generate_series(1,5) b(b);
+ --should fail, non unique column names
+ create function tbl_fce_test(a int)
+ returns table(a int, a int) as $$
+ begin
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ ERROR:  column "a" duplicated
+ -- should runtime error
+ create function tbl_fce_test(a int)
+ returns table(a int, b int) as $$
+ begin
+   -- nonexisting column
+   raise notice '%', b;
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ select * from tbl_fce_test(3) where a > 2;
+ ERROR:  column "b" does not exist
+ LINE 1: SELECT  b
+                 ^
+ QUERY:  SELECT  b
+ CONTEXT:  PL/pgSQL function "tbl_fce_test" line 3 at raise
+ -- should work
+ create or replace function tbl_fce_test(a int)
+ returns table(a int, b int) as $$
+ begin
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ select * from tbl_fce_test(3) where a >= 2;
+  a | b 
+ ---+---
+  2 | 1
+  2 | 2
+  2 | 3
+  2 | 4
+  2 | 5
+ (5 rows)
+ 
+ create function tbl_fce_test2()
+ returns table (o int) as $$
+ begin
+   return table(select a from tbl_test t where t.a < 3);
+ end; 
+ $$ language plpgsql;
+ select * from tbl_fce_test2();
+  o 
+ ---
+  1
+  1
+  1
+  1
+  1
+  2
+  2
+  2
+  2
+  2
+ (10 rows)
+ 
+ drop function tbl_fce_test2();
+ drop function tbl_fce_test(int);
+ drop table tbl_test;
*** ./src/test/regress/sql/plpgsql.sql.orig	2007-04-29 03:21:09.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql	2007-05-06 10:36:29.000000000 +0200
***************
*** 2535,2537 ****
--- 2535,2586 ----
  
  drop function sc_test();
  
+ 
+  
+ -- table functions test
+ create table tbl_test(
+   a int,
+   b int
+ );
+ 
+ insert into tbl_test select a, b from generate_series(1,5) a(a), generate_series(1,5) b(b);
+ 
+ --should fail, non unique column names
+ create function tbl_fce_test(a int)
+ returns table(a int, a int) as $$
+ begin
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ 
+ -- should runtime error
+ create function tbl_fce_test(a int)
+ returns table(a int, b int) as $$
+ begin
+   -- nonexisting column
+   raise notice '%', b;
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ select * from tbl_fce_test(3) where a > 2;
+ 
+ -- should work
+ create or replace function tbl_fce_test(a int)
+ returns table(a int, b int) as $$
+ begin
+   return table(select * from tbl_test t where t.a < a);
+ end;
+ $$ language plpgsql;
+ select * from tbl_fce_test(3) where a >= 2;
+ 
+ create function tbl_fce_test2()
+ returns table (o int) as $$
+ begin
+   return table(select a from tbl_test t where t.a < 3);
+ end; 
+ $$ language plpgsql;
+ select * from tbl_fce_test2();
+ 
+ drop function tbl_fce_test2();
+ drop function tbl_fce_test(int);
+ drop table tbl_test;