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
