Hello
this patch add support of table functions syntax like ANSI SQL 2003.
CREATE OR REPLACE FUNCTION foo_sql(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
SELECT i, i+1, i+2
FROM generate_series(1, $1) g(i);
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION foo_plpgsql1(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
FOR i IN 1..m LOOP
r = ROW(i, i+1, i+2);
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION foo_plpgsql2(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
RETURN QUERY
SELECT i, i+1, i+2
FROM generate_series(1, m) g(i);
RETURN;
END;
$$ LANGUAGE plpgsql;
There are one significant difference to SRF with OUT variables.
Attributies declared in TABLE clause doesn't create local variables.
It's in conformance with SQL/PSM.
Regards
Pavel Stehule
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-09-11 02:06:41.000000000 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-06-03 11:50:25.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
***************
*** 410,415 ****
--- 411,450 ----
</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>
***************
*** 616,622 ****
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>
--- 651,657 ----
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-11-10 21:14:36.000000000 +0100
--- ./doc/src/sgml/xfunc.sgml 2008-06-03 11:50:25.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
***************
*** 729,734 ****
--- 737,800 ----
</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 2008-05-12 02:00:47.000000000 +0200
--- ./src/backend/commands/functioncmds.c 2008-06-03 11:50:25.000000000 +0200
***************
*** 225,240 ****
(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);
--- 225,246 ----
(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 2008-05-17 01:36:05.000000000 +0200
--- ./src/backend/parser/gram.y 2008-06-03 11:50:25.000000000 +0200
***************
*** 113,118 ****
--- 113,121 ----
static Node *makeAArrayExpr(List *elements);
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"
***************
*** 253,259 ****
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
--- 256,262 ----
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
***************
*** 263,268 ****
--- 266,273 ----
%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
***************
*** 4122,4127 ****
--- 4127,4145 ----
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:
***************
*** 4329,4334 ****
--- 4347,4376 ----
| /*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
*
***************
*** 9609,9615 ****
{
FunctionParameter *p = (FunctionParameter *) lfirst(i);
! if (p->mode != FUNC_PARAM_OUT) /* keep if IN or INOUT */
result = lappend(result, p->argType);
}
return result;
--- 9651,9658 ----
{
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;
***************
*** 9792,9797 ****
--- 9835,9901 ----
QueryIsRule = FALSE;
}
+ /* 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 2008-03-25 23:42:45.000000000 +0100
--- ./src/backend/utils/fmgr/funcapi.c 2008-06-03 11:50:25.000000000 +0200
***************
*** 550,556 ****
case ANYELEMENTOID:
case ANYNONARRAYOID:
case ANYENUMOID:
! if (argmode == PROARGMODE_OUT)
have_anyelement_result = true;
else
{
--- 550,556 ----
case ANYELEMENTOID:
case ANYNONARRAYOID:
case ANYENUMOID:
! if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
have_anyelement_result = true;
else
{
***************
*** 565,571 ****
}
break;
case ANYARRAYOID:
! if (argmode == PROARGMODE_OUT)
have_anyarray_result = true;
else
{
--- 565,571 ----
}
break;
case ANYARRAYOID:
! if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
have_anyarray_result = true;
else
{
***************
*** 582,588 ****
default:
break;
}
! if (argmode != PROARGMODE_OUT)
inargno++;
}
--- 582,588 ----
default:
break;
}
! if (argmode != PROARGMODE_OUT && argmode != PROARGMODE_TABLE)
inargno++;
}
***************
*** 847,853 ****
if (argmodes[i] == PROARGMODE_IN)
continue;
Assert(argmodes[i] == PROARGMODE_OUT ||
! argmodes[i] == PROARGMODE_INOUT);
if (++numoutargs > 1)
{
/* multiple out args, so forget it */
--- 847,854 ----
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 */
***************
*** 997,1003 ****
if (argmodes[i] == PROARGMODE_IN)
continue;
Assert(argmodes[i] == PROARGMODE_OUT ||
! argmodes[i] == PROARGMODE_INOUT);
outargtypes[numoutargs] = argtypes[i];
if (argnames)
pname = TextDatumGetCString(argnames[i]);
--- 998,1005 ----
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 = TextDatumGetCString(argnames[i]);
*** ./src/bin/pg_dump/pg_dump.c.orig 2008-05-17 01:36:05.000000000 +0200
--- ./src/bin/pg_dump/pg_dump.c 2008-06-03 11:50:25.000000000 +0200
***************
*** 42,47 ****
--- 42,55 ----
#include "catalog/pg_type.h"
#include "libpq/libpq-fs.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"
***************
*** 169,174 ****
--- 177,187 ----
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);
***************
*** 6444,6449 ****
--- 6457,6466 ----
case 'b':
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 = "";
***************
*** 6469,6474 ****
--- 6486,6558 ----
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
*
***************
*** 6772,6788 ****
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)
--- 6856,6889 ----
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 2008-05-30 00:48:07.000000000 +0200
--- ./src/include/catalog/pg_proc.h 2008-06-03 11:50:25.000000000 +0200
***************
*** 4457,4461 ****
--- 4457,4462 ----
#define PROARGMODE_IN 'i'
#define PROARGMODE_OUT 'o'
#define PROARGMODE_INOUT 'b'
+ #define PROARGMODE_TABLE 't'
#endif /* PG_PROC_H */
*** ./src/include/nodes/parsenodes.h.orig 2008-05-17 01:36:05.000000000 +0200
--- ./src/include/nodes/parsenodes.h 2008-06-03 11:50:26.000000000 +0200
***************
*** 1568,1574 ****
/* 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
--- 1568,1575 ----
/* 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 2008-06-03 11:50:00.000000000 +0200
--- ./src/test/regress/expected/rangefuncs.out 2008-06-03 12:12:56.000000000 +0200
***************
*** 528,530 ****
--- 528,583 ----
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();
*** ./src/test/regress/sql/rangefuncs.sql.orig 2008-06-03 11:50:03.000000000 +0200
--- ./src/test/regress/sql/rangefuncs.sql 2008-06-03 12:13:11.000000000 +0200
***************
*** 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();
+
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
