Hello
This patch contains implementation of table functions defined in ANSI
SQL 2003 (Conformance with SQL2003: T326 Table functions). Patch adds
new proargmode PROARGMODE_TABLE (based on PROARGMODE_OUT). Columns of
output table are similar OUT arguments, but don't generate any
variables.
Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
Description: SIGMOD Record, Vol. 33, No. 1, March 2004
Patch contains three diffs:
* pgdumpclean .. small readability fix for pg_dump.c
* tabfce .. implementation of table functions
* plpgsql-tabfce .. table expression support for plpgsql's return stmt
This patch can broke applications which scan argmodes. Plpgsql support
is separated because plpgsql implementation isn't standardised. But
current plpgsql hasn't any method for one statement table output from
function, and table expression is clean and simple tool for it.
Older version was broken by enum patch and scrollable cursor patch.
Older version didn't dump table functions correctly. It's fixed now.
Regards
Pavel Stehule
*** ./pg_dump.c.orig 2007-05-06 11:13:11.000000000 +0200
--- ./pg_dump.c 2007-05-06 11:16:53.000000000 +0200
***************
*** 5959,5971 ****
{
switch (argmodes[j][0])
{
! case 'i':
argmode = "";
break;
! case 'o':
argmode = "OUT ";
break;
! case 'b':
argmode = "INOUT ";
break;
default:
--- 5959,5971 ----
{
switch (argmodes[j][0])
{
! case PROARGMODE_IN:
argmode = "";
break;
! case PROARGMODE_OUT:
argmode = "OUT ";
break;
! case PROARGMODE_INOUT:
argmode = "INOUT ";
break;
default:
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-04-23 18:52:53.000000000 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2007-05-06 10:00:58.000000000 +0200
***************
*** 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>
***************
*** 564,570 ****
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>
--- 599,605 ----
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-05-06 09:54:41.000000000 +0200
--- ./doc/src/sgml/xfunc.sgml 2007-05-06 10:00:58.000000000 +0200
***************
*** 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-05-06 09:55:04.000000000 +0200
--- ./src/backend/commands/functioncmds.c 2007-05-06 10:00:58.000000000 +0200
***************
*** 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-04-28 00:05:48.000000000 +0200
--- ./src/backend/parser/gram.y 2007-05-06 10:07:12.000000000 +0200
***************
*** 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"
***************
*** 248,254 ****
TableFuncElementList opt_type_modifiers
prep_type_clause
execute_param_clause using_clause returning_clause
! enum_val_list
%type <range> OptTempTableName
%type <into> into_clause create_as_target
--- 251,257 ----
TableFuncElementList opt_type_modifiers
prep_type_clause
execute_param_clause using_clause returning_clause
! enum_val_list tbl_func_column_list
%type <range> OptTempTableName
%type <into> into_clause create_as_target
***************
*** 258,263 ****
--- 261,268 ----
%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
***************
*** 4018,4023 ****
--- 4023,4041 ----
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:
***************
*** 4220,4225 ****
--- 4238,4267 ----
| /*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
*
***************
*** 9417,9423 ****
{
FunctionParameter *p = (FunctionParameter *) lfirst(i);
! if (p->mode != FUNC_PARAM_OUT) /* keep if IN or INOUT */
result = lappend(result, p->argType);
}
return result;
--- 9459,9466 ----
{
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;
***************
*** 9608,9613 ****
--- 9651,9717 ----
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-04-02 05:49:39.000000000 +0200
--- ./src/backend/utils/fmgr/funcapi.c 2007-05-06 10:14:16.000000000 +0200
***************
*** 529,535 ****
{
case ANYELEMENTOID:
case ANYENUMOID:
! if (argmode == PROARGMODE_OUT)
have_anyelement_result = true;
else
{
--- 529,535 ----
{
case ANYELEMENTOID:
case ANYENUMOID:
! if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
have_anyelement_result = true;
else
{
***************
*** 544,550 ****
}
break;
case ANYARRAYOID:
! if (argmode == PROARGMODE_OUT)
have_anyarray_result = true;
else
{
--- 544,550 ----
}
break;
case ANYARRAYOID:
! if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
have_anyarray_result = true;
else
{
***************
*** 561,567 ****
default:
break;
}
! if (argmode != PROARGMODE_OUT)
inargno++;
}
--- 561,567 ----
default:
break;
}
! if (argmode != PROARGMODE_OUT && argmode != PROARGMODE_TABLE)
inargno++;
}
***************
*** 826,832 ****
if (argmodes[i] == PROARGMODE_IN)
continue;
Assert(argmodes[i] == PROARGMODE_OUT ||
! argmodes[i] == PROARGMODE_INOUT);
if (++numoutargs > 1)
{
/* multiple out args, so forget it */
--- 826,833 ----
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 */
***************
*** 977,983 ****
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]));
--- 978,985 ----
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/bin/pg_dump/pg_dump.c.orig 2007-05-06 11:24:29.000000000 +0200
--- ./src/bin/pg_dump/pg_dump.c 2007-05-06 14:33:46.000000000 +0200
***************
*** 49,54 ****
--- 49,62 ----
#include "libpq/libpq-fs.h"
#include "mb/pg_wchar.h"
+ #ifdef USE_ASSERT_CHECKING
+ #include <assert.h>
+ #define psql_assert(p) assert(p)
+ #else
+ #define psql_assert(p)
+ #endif
+
+
#include "pg_backup_archiver.h"
#include "dumputils.h"
***************
*** 171,176 ****
--- 179,189 ----
char **allargtypes,
char **argmodes,
char **argnames);
+ static bool is_returns_table_function(int nallargs, char **argmodes);
+ static char *format_table_function_columns(FuncInfo *finfo, int nallargs,
+ char **allargtypes,
+ char **argmodes,
+ char **argnames);
static char *format_function_signature(FuncInfo *finfo, bool honor_quotes);
static const char *convertRegProcReference(const char *proc);
static const char *convertOperatorReference(const char *opr);
***************
*** 5968,5973 ****
--- 5981,5990 ----
case PROARGMODE_INOUT:
argmode = "INOUT ";
break;
+ case PROARGMODE_TABLE:
+ /* skip table column's names */
+ free(typname);
+ continue;
default:
write_msg(NULL, "WARNING: bogus value in proargmodes array\n");
argmode = "";
***************
*** 5993,5998 ****
--- 6010,6082 ----
return fn.data;
}
+ /*
+ * is_returns_table_function: returns true if function id declared as
+ * RETURNS TABLE, i.e. at least one argument is PROARGMODE_TABLE
+ */
+ static bool
+ is_returns_table_function(int nallargs, char **argmodes)
+ {
+ int j;
+
+ if (argmodes)
+ for (j = 0; j < nallargs; j++)
+ if (argmodes[j][0] == PROARGMODE_TABLE)
+ return true;
+
+ return false;
+ }
+
+
+ /*
+ * format_table_function_columns: generate column list for
+ * table functions.
+ */
+ static char *
+ format_table_function_columns(FuncInfo *finfo, int nallargs,
+ char **allargtypes,
+ char **argmodes,
+ char **argnames)
+ {
+ PQExpBufferData fn;
+ int j;
+ bool first_column = true;
+
+ initPQExpBuffer(&fn);
+ appendPQExpBuffer(&fn, "(");
+
+ /* argmodes is checked in is_returns_table_function */
+ psql_assert(argmodes);
+
+ for (j = 0; j < nallargs; j++)
+ {
+ Oid typid;
+ char *typname;
+
+ /*
+ * argmodes are checked in format_function_arguments. Isn't
+ * neccessery check argmodes here again
+ */
+ if (argmodes[j][0] == PROARGMODE_TABLE)
+ {
+ typid = allargtypes ? atooid(allargtypes[j]) : finfo->argtypes[j];
+ typname = getFormattedTypeName(typid, zeroAsOpaque);
+
+ /* column's name is always NOT NULL (checked in gram.y) */
+ appendPQExpBuffer(&fn, "%s%s %s",
+ first_column ? "" : ", ",
+ fmtId(argnames[j]),
+ typname);
+ free(typname);
+ first_column = false;
+ }
+ }
+
+ appendPQExpBuffer(&fn, ")");
+ return fn.data;
+ }
+
+
/*
* format_function_signature: generate function name and argument list
*
***************
*** 6279,6295 ****
fmtId(finfo->dobj.namespace->dobj.name),
funcsig);
- rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
- appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
- appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
- (proretset[0] == 't') ? "SETOF " : "",
- rettypename,
- asPart->data,
- fmtId(lanname));
! free(rettypename);
if (provolatile[0] != PROVOLATILE_VOLATILE)
{
if (provolatile[0] == PROVOLATILE_IMMUTABLE)
--- 6363,6396 ----
fmtId(finfo->dobj.namespace->dobj.name),
funcsig);
! appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
+ /* swich between RETURNS SETOF RECORD and RETURNS TABLE functions */
+ if (!is_returns_table_function(nallargs, argmodes))
+ {
+ rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
+ appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
+ (proretset[0] == 't') ? "SETOF " : "",
+ rettypename,
+ asPart->data,
+ fmtId(lanname));
+ free(rettypename);
+ }
+ else
+ {
+ char *func_cols;
+
+ func_cols = format_table_function_columns(finfo, nallargs, allargtypes,
+ argmodes, argnames);
+ appendPQExpBuffer(q, "RETURNS TABLE %s\n %s\n LANGUAGE %s",
+ func_cols,
+ asPart->data,
+ fmtId(lanname));
+ free(func_cols);
+ }
+
if (provolatile[0] != PROVOLATILE_VOLATILE)
{
if (provolatile[0] == PROVOLATILE_IMMUTABLE)
*** ./src/include/catalog/pg_proc.h.orig 2007-04-02 05:49:40.000000000 +0200
--- ./src/include/catalog/pg_proc.h 2007-05-06 10:00:59.000000000 +0200
***************
*** 4209,4214 ****
--- 4209,4215 ----
#define PROARGMODE_IN 'i'
#define PROARGMODE_OUT 'o'
#define PROARGMODE_INOUT 'b'
+ #define PROARGMODE_TABLE 't'
/*
*** ./src/include/nodes/parsenodes.h.orig 2007-04-28 00:05:49.000000000 +0200
--- ./src/include/nodes/parsenodes.h 2007-05-06 10:00:59.000000000 +0200
***************
*** 1526,1532 ****
/* 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
--- 1526,1533 ----
/* 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/test/regress/expected/rangefuncs.out.orig 2007-04-02 05:49:42.000000000 +0200
--- ./src/test/regress/expected/rangefuncs.out 2007-05-06 10:47:20.000000000 +0200
***************
*** 528,530 ****
--- 528,636 ----
AS 'select $1, array[$1,$1]' LANGUAGE sql;
ERROR: cannot determine result data type
DETAIL: A function returning a polymorphic type must have at least one polymorphic argument.
+ --
+ -- 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();
+ --
+ -- 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/rangefuncs.sql.orig 2007-05-06 09:57:38.000000000 +0200
--- ./src/test/regress/sql/rangefuncs.sql 2007-05-06 10:37:26.000000000 +0200
***************
*** 261,263 ****
--- 261,297 ----
-- 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();
+
+ --
+ -- 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();
*** ./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;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
