2008/7/10 Marko Kreen <[EMAIL PROTECTED]>:
> On 7/10/08, Pavel Stehule <[EMAIL PROTECTED]> wrote:
>> I am sending actualized patch
>>
>> Regards
>> Pavel Stehule
>>
>> 2008/7/9 Pavel Stehule <[EMAIL PROTECTED]>:
>>
>> > 2008/7/9 Marko Kreen <[EMAIL PROTECTED]>:
>> >> Generally, the patch looks fine. There are few issues still:
>> >>
>> >> - plpgsql: the result columns _do_ create local variables.
>> >> AIUI, they should not?
>> >
>> > it was my mistake - it doesn't do local variables - fixed
>> >>
>> >> - pg_dump: is the psql_assert() introduction necessary, considering it
>> >> is used only in one place?
>> >
>> > removed - argmode variables is checked before
>> >>
>> >> - There should be regression test for plpgsql too, that test if
>> >> the behaviour is correct.
>> >>
>> >
>> > addeded
>> >> - The documentation should mention behaviour difference from OUT
>> >> parameters.
>> >
>> > I will do it.
>> >>
>> >> Wishlist (probably out of scope for this patch):
>> >
>> > this is in my wishlist too, but postgresql doesn't support types like
>> > result of functions.
>> >>
>> >> - plpgsql: a way to create record variable for result row. Something
>> like:
>> >>
>> >> CREATE FUNCTION foo(..) RETURNS TABLE (..) AS $$
>> >> DECLARE
>> >> retval foo%ROWTYPE;
>> >>
>> >>
>> >> Currently the OUT parameters are quite painful to use due to bad
>> >> name resolving logic. Such feature would be perfect replacement.
>> >>
>> >> --
>> >> marko
>> >>
>> > I'll send patch early, thank you much
>
> Ok, last items:
>
> - Attached is a patch that fixes couple C comments.
>
> - I think plpgsql 38.1.2 chapter of "Supported Argument and Result Data
> Types" should also have a mention of TABLE functions.
>
> Then I'm content with the patch.
>
applyed
Regards and thank you very much
Pavel
> --
> marko
>
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-09-11 02:06:41.000000000 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-07-09 16:34:26.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-07-10 10:57:46.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,807 ----
</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>
+ <para>
+ For SQL language there are not significant differencies between table
+ functions and set's returning functions. Table functions are ANSI SQL
+ conformant, set's returning functions are not. Attributies included in
+ column list are not related with any variables, that is major diffirence
+ between table functions and set's returning functions in other PL languages.
+ </para>
+ </sect2>
+
+ <sect2>
<title>Polymorphic <acronym>SQL</acronym> Functions</title>
<para>
*** ./src/backend/commands/functioncmds.c.orig 2008-06-19 02:46:04.000000000 +0200
--- ./src/backend/commands/functioncmds.c 2008-07-09 16:34:26.000000000 +0200
***************
*** 226,241 ****
(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);
--- 226,247 ----
(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-06-15 03:25:54.000000000 +0200
--- ./src/backend/parser/gram.y 2008-07-09 16:34:26.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
***************
*** 4130,4135 ****
--- 4135,4153 ----
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:
***************
*** 4337,4342 ****
--- 4355,4384 ----
| /*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
*
***************
*** 9625,9631 ****
{
FunctionParameter *p = (FunctionParameter *) lfirst(i);
! if (p->mode != FUNC_PARAM_OUT) /* keep if IN or INOUT */
result = lappend(result, p->argType);
}
return result;
--- 9667,9674 ----
{
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;
***************
*** 9808,9813 ****
--- 9851,9917 ----
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-07-09 16:34:26.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-07-01 13:46:48.000000000 +0200
--- ./src/bin/pg_dump/pg_dump.c 2008-07-10 15:35:42.000000000 +0200
***************
*** 169,174 ****
--- 169,179 ----
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 ****
--- 6449,6458 ----
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 ****
--- 6478,6547 ----
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, "(");
+
+ 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,6784 ****
fmtId(finfo->dobj.namespace->dobj.name),
funcsig);
- rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
-
appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
! appendPQExpBuffer(q, "RETURNS %s%s",
! (proretset[0] == 't') ? "SETOF " : "",
! rettypename);
! free(rettypename);
appendPQExpBuffer(q, "\n LANGUAGE %s", fmtId(lanname));
if (provolatile[0] != PROVOLATILE_VOLATILE)
--- 6845,6870 ----
fmtId(finfo->dobj.namespace->dobj.name),
funcsig);
appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
!
! /* Switch 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",
! (proretset[0] == 't') ? "SETOF " : "",
! rettypename);
! free(rettypename);
! }
! else
! {
! char *func_cols;
!
! func_cols = format_table_function_columns(finfo, nallargs, allargtypes,
! argmodes, argnames);
! appendPQExpBuffer(q, "RETURNS TABLE %s", func_cols);
! free(func_cols);
! }
appendPQExpBuffer(q, "\n LANGUAGE %s", fmtId(lanname));
if (provolatile[0] != PROVOLATILE_VOLATILE)
***************
*** 6803,6809 ****
/*
* COST and ROWS are emitted only if present and not default, so as not to
! * break backwards-compatibility of the dump without need. Keep this code
* in sync with the defaults in functioncmds.c.
*/
if (strcmp(procost, "0") != 0)
--- 6889,6895 ----
/*
* COST and ROWS are emitted only if present and not default, so as not to
! * break backwards-compatibility of the dump without need. Keep this code
* in sync with the defaults in functioncmds.c.
*/
if (strcmp(procost, "0") != 0)
*** ./src/include/catalog/pg_proc.h.orig 2008-07-03 22:58:46.000000000 +0200
--- ./src/include/catalog/pg_proc.h 2008-07-09 16:34:26.000000000 +0200
***************
*** 4469,4473 ****
--- 4469,4474 ----
#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-07-09 16:34: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/pl/plpgsql/src/pl_comp.c.orig 2008-07-09 17:11:56.000000000 +0200
--- ./src/pl/plpgsql/src/pl_comp.c 2008-07-10 15:34:27.000000000 +0200
***************
*** 402,407 ****
--- 402,411 ----
PLpgSQL_variable *argvariable;
int argitemtype;
+ /* PROARGMODE_TABLE params should not create local variables */
+ if (argmode == PROARGMODE_TABLE)
+ continue;
+
/* Create $n name for variable */
snprintf(buf, sizeof(buf), "$%d", i + 1);
*** ./src/test/regress/expected/plpgsql.out.orig 2008-07-09 17:45:32.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2008-07-09 17:42:02.000000000 +0200
***************
*** 3544,3546 ****
--- 3544,3575 ----
drop function catch();
drop function case_test(bigint);
+ -- table function test
+ create or replace function tftest(int) returns table(a int) as $$
+ begin
+ return query select $1;
+ return;
+ end;
+ $$ language plpgsql immutable strict;
+ select * from tftest(10);
+ a
+ ----
+ 10
+ (1 row)
+
+ drop function tftest(int);
+ --should to fail, table params not accessable
+ create or replace function tftest(a1 int) returns table(a int) as $$
+ begin
+ raise notice 'IN param %', a1;
+ raise notice 'TABLE paam %', a;
+ end;
+ $$ language plpgsql immutable strict;
+ select * from tftest(10);
+ NOTICE: IN param 10
+ ERROR: column "a" does not exist
+ LINE 1: SELECT a
+ ^
+ QUERY: SELECT a
+ CONTEXT: PL/pgSQL function "tftest" line 3 at RAISE
+ drop function tftest(int);
*** ./src/test/regress/expected/rangefuncs.out.orig 2008-07-09 15:33:23.000000000 +0200
--- ./src/test/regress/expected/rangefuncs.out 2008-07-09 16:34:26.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/plpgsql.sql.orig 2008-07-09 17:27:27.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2008-07-09 17:40:29.000000000 +0200
***************
*** 2879,2881 ****
--- 2879,2900 ----
drop function catch();
drop function case_test(bigint);
+ -- table function test
+ create or replace function tftest(int) returns table(a int) as $$
+ begin
+ return query select $1;
+ return;
+ end;
+ $$ language plpgsql immutable strict;
+ select * from tftest(10);
+ drop function tftest(int);
+
+ --should to fail, table params not accessable
+ create or replace function tftest(a1 int) returns table(a int) as $$
+ begin
+ raise notice 'IN param %', a1;
+ raise notice 'TABLE paam %', a;
+ end;
+ $$ language plpgsql immutable strict;
+ select * from tftest(10);
+ drop function tftest(int);
*** ./src/test/regress/sql/rangefuncs.sql.orig 2008-07-09 15:33:30.000000000 +0200
--- ./src/test/regress/sql/rangefuncs.sql 2008-07-09 16:34: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();
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
