Hello

this patch allows using SQL2003 syntax for set returning functions. It is based on using new type of argmode - PROARGMODE_TABLE.


Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php

Sample:

CREATE FUNCTION foof(a int)
RETURNS TABLE(a int, b int) AS
$$ SELECT x, y FROM Foo WHERE x < a $$ LANGUAGE sql;

CREATE FUNCTION fooff(a int)
RETURNS TABLE(a int, b int) AS $$
BEGIN
 RETURN TABLE(SELECT * FRON Foo WHERE x < a);
END; $$ LANGUAGE plpgsql;

This patch enhance plpgsql stmt return too (table expression support).

Conformance with SQL2003:
T326    Table functions

Description: SIGMOD Record, Vol. 33, No. 1, March 2004

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig	2007-02-11 12:01:48.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml	2007-02-11 18:54:16.000000000 +0100
***************
*** 1567,1575 ****
      <sect3>
       <title><command>RETURN</></title>
  
! <synopsis>
! RETURN <replaceable>expression</replaceable>;
! </synopsis>
  
       <para>
        <command>RETURN</command> with an expression terminates the
--- 1567,1580 ----
      <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
***************
*** 1594,1599 ****
--- 1599,1613 ----
       </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
*** ./doc/src/sgml/ref/create_function.sgml.orig	2007-02-11 09:55:06.000000000 +0100
--- ./doc/src/sgml/ref/create_function.sgml	2007-02-11 10:28:08.000000000 +0100
***************
*** 21,27 ****
  <synopsis>
  CREATE [ OR REPLACE ] FUNCTION
      <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] )
!     [ RETURNS <replaceable class="parameter">rettype</replaceable> ]
    { LANGUAGE <replaceable class="parameter">langname</replaceable>
      | IMMUTABLE | STABLE | VOLATILE
      | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28 ----
  <synopsis>
  CREATE [ OR REPLACE ] FUNCTION
      <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] )
!     [ RETURNS <replaceable class="parameter">rettype</replaceable> 
!       | RETURNS TABLE ( <replaceable class="parameter">colname</replaceable> <replaceable class="parameter">coltype</replaceable> [, ...] ) ]
    { LANGUAGE <replaceable class="parameter">langname</replaceable>
      | IMMUTABLE | STABLE | VOLATILE
      | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***************
*** 387,392 ****
--- 388,427 ----
      </listitem>
     </varlistentry>
  
+     <varlistentry>
+      <term><replaceable class="parameter">colname</replaceable></term>
+ 
+      <listitem>
+       <para>
+        The name of an output table column.
+       </para>
+      </listitem>
+     </varlistentry>
+ 
+     <varlistentry>
+      <term><replaceable class="parameter">coltype</replaceable></term>
+ 
+      <listitem>
+       <para>
+        The data type(s) of output table column.
+       </para>
+       <para>
+        Depending on the implementation language it might also be allowed
+        to specify <quote>pseudotypes</> such as <type>cstring</>.
+        Pseudotypes indicate that the actual argument type is either
+        incompletely specified, or outside the set of ordinary SQL data types.
+       </para>
+       <para>
+        The type of a column is referenced by writing
+        <literal><replaceable
+        class="parameter">tablename</replaceable>.<replaceable
+        class="parameter">columnname</replaceable>%TYPE</literal>.
+        Using this feature can sometimes help make a function independent of
+        changes to the definition of a table.
+       </para>
+      </listitem>
+     </varlistentry>
+ 
     </variablelist>
  
   </refsect1>
***************
*** 516,522 ****
     A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later.
     The <productname>PostgreSQL</productname> version is similar but
     not fully compatible.  The attributes are not portable, neither are the
!    different available languages.
    </para>
  
    <para>
--- 551,557 ----
     A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later.
     The <productname>PostgreSQL</productname> version is similar but
     not fully compatible.  The attributes are not portable, neither are the
!    different available languages. TABLE functions are defined in SQL:2003.
    </para>
  
    <para>
*** ./doc/src/sgml/xfunc.sgml.orig	2007-02-11 09:18:54.000000000 +0100
--- ./doc/src/sgml/xfunc.sgml	2007-02-11 09:45:11.000000000 +0100
***************
*** 102,107 ****
--- 102,115 ----
     </para>
  
     <para>
+     <indexterm><primary>TABLE</><seealso>function</></>An SQL function can
+     declared to return table specified by function's retun table as 
+     <literal>TABLE(<replaceable>somecolumns</>)</literal>. In this case
+     all rows of the last query's result are returned. Furteher details
+     appear bellow.
+   </para>
+ 
+    <para>
      The body of an SQL function must be a list of SQL
      statements separated by semicolons.  A semicolon after the last
      statement is optional.  Unless the function is declared to return
***************
*** 713,718 ****
--- 721,784 ----
     </sect2>
  
     <sect2>
+     <title><acronym>SQL</acronym> Table Functions</title>
+ 
+     <para>
+      When an SQL function is declared as returning 
+     <literal>TABLE(<replaceable>somecolumns</>)</literal>, the function's final
+      <command>SELECT</> 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</>
+      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</> has the same contents as above, and we say:
+ 
+ <programlisting>
+ CREATE FUNCTION getfoo(int) 
+ RETURNS TABLE(id integer, subid integer, name varchar) AS $$
+     SELECT * FROM foo WHERE fooid = $1;
+ $$ LANGUAGE SQL;
+ 
+ SELECT * FROM getfoo(1) AS t1;
+ </programlisting>
+ 
+      Then we would get:
+ <screen>
+   id | subid | name
+ -----+-------+------
+    1 |     1 | Joe
+    1 |     2 | Ed
+ (2 rows)
+ </screen>
+     </para>
+ 
+     <para>
+      Returned table can have one or more columns;
+ 
+ <programlisting>
+ CREATE FUNCTION listchildren(text) RETURNS TABLE(name text) AS $$
+     SELECT name FROM nodes WHERE parent = $1
+ $$ LANGUAGE SQL;
+ 
+ SELECT * FROM listchildren('Top');
+ </programlisting>
+ 
+      Then we would get:
+ <screen>
+  name
+ --------
+  Child1
+  Child2
+  Child3
+ (3 rows)
+ </screen>
+     </para>
+    </sect2>
+ 
+    <sect2>
      <title>Polymorphic <acronym>SQL</acronym> Functions</title>
  
      <para>
*** ./src/backend/commands/functioncmds.c.orig	2007-02-09 23:16:48.000000000 +0100
--- ./src/backend/commands/functioncmds.c	2007-02-09 23:39:51.000000000 +0100
***************
*** 203,218 ****
  					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					 errmsg("functions cannot accept set arguments")));
  
! 		if (fp->mode != FUNC_PARAM_OUT)
! 			inTypes[inCount++] = toid;
! 
! 		if (fp->mode != FUNC_PARAM_IN)
  		{
! 			if (outCount == 0)	/* save first OUT param's type */
! 				*requiredResultType = toid;
! 			outCount++;
  		}
  
  		allTypes[i] = ObjectIdGetDatum(toid);
  
  		paramModes[i] = CharGetDatum(fp->mode);
--- 203,224 ----
  					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					 errmsg("functions cannot accept set arguments")));
  
! 		switch (fp->mode)
  		{
! 			case FUNC_PARAM_IN:
! 				inTypes[inCount++] = toid;
! 				break;
! 			case FUNC_PARAM_INOUT:
! 				inTypes[inCount++] = toid;
! 			case FUNC_PARAM_OUT:
! 			case FUNC_PARAM_TABLE:
! 				/* save first OUT param's type */
! 				if (outCount == 0)
! 					*requiredResultType = toid;
! 				outCount++;
  		}
  
+ 
  		allTypes[i] = ObjectIdGetDatum(toid);
  
  		paramModes[i] = CharGetDatum(fp->mode);
*** ./src/backend/parser/gram.y.orig	2007-02-09 22:44:53.000000000 +0100
--- ./src/backend/parser/gram.y	2007-02-10 17:18:53.000000000 +0100
***************
*** 109,114 ****
--- 109,117 ----
  static void doNegateFloat(Value *v);
  static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args);
  
+ static List *mergeTblFuncParameters(List *func_args, List *columns);
+ static TypeName *TblFuncTypeName(List *colums);
+ 
  %}
  
  %name-prefix="base_yy"
***************
*** 247,252 ****
--- 250,256 ----
  				TableFuncElementList opt_type_modifiers
  				prep_type_clause
  				execute_param_clause using_clause returning_clause
+ 				tbl_func_column_list
  
  %type <range>	into_clause OptTempTableName
  
***************
*** 255,260 ****
--- 259,266 ----
  %type <fun_param_mode> arg_class
  %type <typnam>	func_return func_type
  
+ %type <fun_param> tbl_func_column
+ 
  %type <boolean>  TriggerForType OptTemp
  %type <oncommit> OnCommitOption
  
***************
*** 3906,3911 ****
--- 3912,3930 ----
  					n->withClause = $7;
  					$$ = (Node *)n;
  				}
+ 			| CREATE opt_or_replace FUNCTION func_name func_args
+ 			  RETURNS TABLE '(' tbl_func_column_list ')' createfunc_opt_list opt_definition
+ 				{
+ 					CreateFunctionStmt *n = makeNode(CreateFunctionStmt);
+ 					n->replace = $2;
+ 					n->funcname = $4;
+ 					n->parameters = mergeTblFuncParameters($5, $9);
+ 					n->returnType = TblFuncTypeName($9);
+ 					n->returnType->location = @8;
+ 					n->options = $11;
+ 					n->withClause = $12;
+ 					$$ = (Node *)n;
+ 				}
  		;
  
  opt_or_replace:
***************
*** 4108,4113 ****
--- 4127,4156 ----
  			| /*EMPTY*/								{ $$ = NIL; }
  		;
  
+ /*
+  * Culumn list for table function definition
+  */
+ tbl_func_column_list:
+ 			tbl_func_column
+ 				{
+ 					$$ = list_make1($1);
+ 				}
+ 			| tbl_func_column_list ',' tbl_func_column
+ 				{
+ 					$$ = lappend($1, $3);
+ 				}
+ 		;
+ 
+ tbl_func_column:	param_name func_type
+ 				{
+ 					FunctionParameter *n = makeNode(FunctionParameter);
+ 					n->name = $1;
+ 					n->argType = $2;
+ 					n->mode = FUNC_PARAM_TABLE;
+ 					$$ = n;
+ 				}
+ 		;
+ 
  /*****************************************************************************
   * ALTER FUNCTION
   *
***************
*** 9280,9286 ****
  	{
  		FunctionParameter *p = (FunctionParameter *) lfirst(i);
  
! 		if (p->mode != FUNC_PARAM_OUT)			/* keep if IN or INOUT */
  			result = lappend(result, p->argType);
  	}
  	return result;
--- 9323,9330 ----
  	{
  		FunctionParameter *p = (FunctionParameter *) lfirst(i);
  
! 		/* keep if IN or INOUT */
! 		if (p->mode != FUNC_PARAM_OUT && p->mode != FUNC_PARAM_TABLE)
  			result = lappend(result, p->argType);
  	}
  	return result;
***************
*** 9471,9476 ****
--- 9515,9581 ----
  	return (Node *) x;
  }
  
+ /* mergeTblFuncParameters check only FUNC_PARAM_IN params in func_args list.
+  * Next check duplicate column names. Returns joined list.
+  */
+ static List *
+ mergeTblFuncParameters(List *func_args, List *columns)
+ {
+ 	ListCell   *i;
+ 
+ 	foreach(i, func_args)
+ 	{
+ 		FunctionParameter *p = (FunctionParameter *) lfirst(i);
+ 
+ 		if (p->mode != FUNC_PARAM_IN)		
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_SYNTAX_ERROR),
+ 					 errmsg("OUT or INOUT arguments aren't allowed in TABLE function")));
+ 	}
+ 
+         /* Check for duplicate names in the explicit list of columns.
+          */                                                                                                             
+         foreach(i, columns)                                                                                          
+         {                                                                                                               
+                 FunctionParameter *p = (FunctionParameter *) lfirst(i);
+                 ListCell   *rest;
+ 
+                 for_each_cell(rest, lnext(i))
+                 {                                                                                                       
+                         FunctionParameter  *rp = (FunctionParameter *) lfirst(rest);
+ 
+                         if (strcmp(p->name, rp->name) == 0)
+                                 ereport(ERROR,
+                                                 (errcode(ERRCODE_DUPLICATE_COLUMN),
+                                                  errmsg("column \"%s\" duplicated",
+                                                                 p->name)));
+                 }
+         }
+ 
+ 	return list_concat(func_args, columns);
+ }
+ 
+ /* Returns correct return type for TABLE function. For list of length one returns
+  * correct type, For longer list returns record
+  */
+ static TypeName *
+ TblFuncTypeName(List *columns)
+ {
+ 	TypeName *result;
+ 
+ 	if (list_length(columns) > 1)
+ 		result = makeTypeName("record");
+ 	else
+ 	{
+ 		FunctionParameter *p = (FunctionParameter *) linitial(columns);
+ 		result = (TypeName *) copyObject(p->argType);
+ 	}
+ 
+ 	result->setof = true;
+ 
+ 	return result;
+ }
+ 
  /*
   * Must undefine base_yylex before including scan.c, since we want it
   * to create the function base_yylex not filtered_base_yylex.
*** ./src/backend/utils/fmgr/funcapi.c.orig	2007-02-10 14:36:42.000000000 +0100
--- ./src/backend/utils/fmgr/funcapi.c	2007-02-10 14:41:31.000000000 +0100
***************
*** 517,523 ****
  		switch (argtypes[i])
  		{
  			case ANYELEMENTOID:
! 				if (argmode == PROARGMODE_OUT)
  					have_anyelement_result = true;
  				else
  				{
--- 517,523 ----
  		switch (argtypes[i])
  		{
  			case ANYELEMENTOID:
! 				if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
  					have_anyelement_result = true;
  				else
  				{
***************
*** 532,538 ****
  				}
  				break;
  			case ANYARRAYOID:
! 				if (argmode == PROARGMODE_OUT)
  					have_anyarray_result = true;
  				else
  				{
--- 532,538 ----
  				}
  				break;
  			case ANYARRAYOID:
! 				if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
  					have_anyarray_result = true;
  				else
  				{
***************
*** 549,555 ****
  			default:
  				break;
  		}
! 		if (argmode != PROARGMODE_OUT)
  			inargno++;
  	}
  
--- 549,555 ----
  			default:
  				break;
  		}
! 		if (argmode != PROARGMODE_OUT && argmode != PROARGMODE_TABLE)
  			inargno++;
  	}
  
***************
*** 810,816 ****
  			if (argmodes[i] == PROARGMODE_IN)
  				continue;
  			Assert(argmodes[i] == PROARGMODE_OUT ||
! 				   argmodes[i] == PROARGMODE_INOUT);
  			if (++numoutargs > 1)
  			{
  				/* multiple out args, so forget it */
--- 810,817 ----
  			if (argmodes[i] == PROARGMODE_IN)
  				continue;
  			Assert(argmodes[i] == PROARGMODE_OUT ||
! 				   argmodes[i] == PROARGMODE_INOUT || 
! 				   argmodes[i] == PROARGMODE_TABLE);
  			if (++numoutargs > 1)
  			{
  				/* multiple out args, so forget it */
***************
*** 961,967 ****
  		if (argmodes[i] == PROARGMODE_IN)
  			continue;
  		Assert(argmodes[i] == PROARGMODE_OUT ||
! 			   argmodes[i] == PROARGMODE_INOUT);
  		outargtypes[numoutargs] = argtypes[i];
  		if (argnames)
  			pname = DatumGetCString(DirectFunctionCall1(textout, argnames[i]));
--- 962,969 ----
  		if (argmodes[i] == PROARGMODE_IN)
  			continue;
  		Assert(argmodes[i] == PROARGMODE_OUT ||
! 			   argmodes[i] == PROARGMODE_INOUT || 
! 			   argmodes[i] == PROARGMODE_TABLE);
  		outargtypes[numoutargs] = argtypes[i];
  		if (argnames)
  			pname = DatumGetCString(DirectFunctionCall1(textout, argnames[i]));
*** ./src/include/catalog/pg_proc.h.orig	2007-02-10 14:34:55.000000000 +0100
--- ./src/include/catalog/pg_proc.h	2007-02-10 14:35:36.000000000 +0100
***************
*** 4100,4105 ****
--- 4100,4106 ----
  #define PROARGMODE_IN		'i'
  #define PROARGMODE_OUT		'o'
  #define PROARGMODE_INOUT	'b'
+ #define PROARGMODE_TABLE	't'
  
  
  /*
*** ./src/include/nodes/parsenodes.h.orig	2007-02-09 23:14:56.000000000 +0100
--- ./src/include/nodes/parsenodes.h	2007-02-09 23:15:59.000000000 +0100
***************
*** 1554,1560 ****
  	/* the assigned enum values appear in pg_proc, don't change 'em! */
  	FUNC_PARAM_IN = 'i',		/* input only */
  	FUNC_PARAM_OUT = 'o',		/* output only */
! 	FUNC_PARAM_INOUT = 'b'		/* both */
  } FunctionParameterMode;
  
  typedef struct FunctionParameter
--- 1554,1561 ----
  	/* the assigned enum values appear in pg_proc, don't change 'em! */
  	FUNC_PARAM_IN = 'i',		/* input only */
  	FUNC_PARAM_OUT = 'o',		/* output only */
! 	FUNC_PARAM_INOUT = 'b',		/* both */
! 	FUNC_PARAM_TABLE = 't'		/* table function column */
  } FunctionParameterMode;
  
  typedef struct FunctionParameter
*** ./src/pl/plpgsql/src/gram.y.orig	2007-02-10 18:14:34.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y	2007-02-10 18:33:23.000000000 +0100
***************
*** 30,36 ****
  static	PLpgSQL_type	*read_datatype(int tok);
  static	PLpgSQL_stmt	*make_execsql_stmt(const char *sqlstart, int lineno);
  static	PLpgSQL_stmt	*make_fetch_stmt(int lineno, int curvar);
! 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	*make_fetch_stmt(int lineno, int curvar);
! 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,
***************
*** 189,194 ****
--- 189,195 ----
  %token	K_RETURN
  %token	K_REVERSE
  %token	K_STRICT
+ %token	K_TABLE
  %token	K_THEN
  %token	K_TO
  %token	K_TYPE
***************
*** 1161,1170 ****
  						{
  							$$ = make_return_next_stmt($2);
  						}
  						else
  						{
  							plpgsql_push_back_token(tok);
! 							$$ = make_return_stmt($2);
  						}
  					}
  				;
--- 1162,1176 ----
  						{
  							$$ = 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);
  						}
  					}
  				;
***************
*** 1997,2003 ****
  
  
  static PLpgSQL_stmt *
! make_return_stmt(int lineno)
  {
  	PLpgSQL_stmt_return *new;
  
--- 2003,2009 ----
  
  
  static PLpgSQL_stmt *
! make_return_stmt(int lineno, bool is_tblexpr)
  {
  	PLpgSQL_stmt_return *new;
  
***************
*** 2006,2013 ****
  	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");
--- 2012,2033 ----
  	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-02-10 14:42:30.000000000 +0100
--- ./src/pl/plpgsql/src/pl_comp.c	2007-02-10 14:57:41.000000000 +0100
***************
*** 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-02-10 18:14:43.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c	2007-02-10 20:11:34.000000000 +0100
***************
*** 174,179 ****
--- 174,180 ----
  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);
  
  
  /* ----------
***************
*** 1851,1857 ****
--- 1852,1868 ----
  	 * 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;
***************
*** 4884,4886 ****
--- 4895,4959 ----
  		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-02-10 21:44:56.000000000 +0100
--- ./src/pl/plpgsql/src/pl_funcs.c	2007-02-10 21:46:31.000000000 +0100
***************
*** 813,818 ****
--- 813,820 ----
  {
  	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-02-10 18:26:33.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h	2007-02-10 18:27:09.000000000 +0100
***************
*** 476,481 ****
--- 476,482 ----
  	int			lineno;
  	PLpgSQL_expr *expr;
  	int			retvarno;
+ 	bool			is_tblexpr;
  } PLpgSQL_stmt_return;
  
  typedef struct
*** ./src/pl/plpgsql/src/scan.l.orig	2007-02-10 18:29:28.000000000 +0100
--- ./src/pl/plpgsql/src/scan.l	2007-02-10 18:30:16.000000000 +0100
***************
*** 156,161 ****
--- 156,162 ----
  reverse			{ return K_REVERSE;			}
  row_count		{ return K_ROW_COUNT;		}
  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-02-10 22:01:18.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out	2007-02-10 22:26:23.000000000 +0100
***************
*** 2934,2936 ****
--- 2934,3006 ----
  ERROR:  query returned more than one row
  CONTEXT:  PL/pgSQL function "footest" line 4 at execute statement
  drop function footest();
+ -- 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/expected/rangefuncs.out.orig	2007-02-10 22:41:43.000000000 +0100
--- ./src/test/regress/expected/rangefuncs.out	2007-02-10 22:40:36.000000000 +0100
***************
*** 528,530 ****
--- 528,583 ----
  AS 'select $1, array[$1,$1]' LANGUAGE sql;
  ERROR:  cannot determine result data type
  DETAIL:  A function returning "anyarray" or "anyelement" must have at least one argument of either type.
+ --
+ -- table functions
+ --
+ CREATE OR REPLACE FUNCTION foo()
+ RETURNS TABLE(a int) 
+ AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
+ SELECT * FROM foo();
+  a 
+ ---
+  1
+  2
+  3
+  4
+  5
+ (5 rows)
+ 
+ DROP FUNCTION foo();
+ CREATE OR REPLACE FUNCTION foo()
+ RETURNS TABLE(a int, b int) 
+ AS $$ SELECT a, b
+          FROM generate_series(1,5) a(a),
+               generate_series(1,5) b(b) $$ LANGUAGE sql;
+ SELECT * FROM foo();
+  a | b 
+ ---+---
+  1 | 1
+  1 | 2
+  1 | 3
+  1 | 4
+  1 | 5
+  2 | 1
+  2 | 2
+  2 | 3
+  2 | 4
+  2 | 5
+  3 | 1
+  3 | 2
+  3 | 3
+  3 | 4
+  3 | 5
+  4 | 1
+  4 | 2
+  4 | 3
+  4 | 4
+  4 | 5
+  5 | 1
+  5 | 2
+  5 | 3
+  5 | 4
+  5 | 5
+ (25 rows)
+ 
+ DROP FUNCTION foo();
*** ./src/test/regress/sql/plpgsql.sql.orig	2007-02-10 21:52:59.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql	2007-02-10 22:25:20.000000000 +0100
***************
*** 2440,2442 ****
--- 2440,2492 ----
  select footest();
  
  drop function footest();
+ 
+ 
+ -- 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;
+ 
*** ./src/test/regress/sql/rangefuncs.sql.orig	2007-02-10 22:35:04.000000000 +0100
--- ./src/test/regress/sql/rangefuncs.sql	2007-02-10 22:39:29.000000000 +0100
***************
*** 261,263 ****
--- 261,281 ----
  -- fails, no way to deduce outputs
  CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
  AS 'select $1, array[$1,$1]' LANGUAGE sql;
+ 
+ --
+ -- table functions
+ --
+ CREATE OR REPLACE FUNCTION foo()
+ RETURNS TABLE(a int) 
+ AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
+ SELECT * FROM foo();
+ DROP FUNCTION foo();
+ 
+ CREATE OR REPLACE FUNCTION foo()
+ RETURNS TABLE(a int, b int) 
+ AS $$ SELECT a, b
+          FROM generate_series(1,5) a(a),
+               generate_series(1,5) b(b) $$ LANGUAGE sql;
+ SELECT * FROM foo();
+ DROP FUNCTION foo();
+ 

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Reply via email to