Re: review: table function support
|
View:
New views
5 Messages
—
Rating Filter:
Alert me
|
 |
|
|
|
Re: review: table function support
by Marko Kreen-2
::
Rate this Message:
On 7/10/08, Pavel Stehule <pavel.stehule@...> wrote:
> I am sending actualized patch > > Regards > Pavel Stehule > > 2008/7/9 Pavel Stehule <pavel.stehule@...>: > > > 2008/7/9 Marko Kreen <markokr@...>: > >> 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 - 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. -- marko [tbl.comments.diff] diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 3f90c93..6ae4521 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6847,7 +6847,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig); - /* swich between RETURNS SETOF RECORD and RETURNS TABLE functions */ + /* switch between RETURNS SETOF RECORD and RETURNS TABLE functions */ if (!is_returns_table_function(nallargs, argmodes)) { rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque); diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index 612a84f..c683aea 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -402,7 +402,7 @@ do_compile(FunctionCallInfo fcinfo, PLpgSQL_variable *argvariable; int argitemtype; - /* skip PROARGMODE_TABLE params - these params hasn't local variables */ + /* PROARGMODE_TABLE params should not create local variables */ if (argmode == PROARGMODE_TABLE) continue; -- Sent via pgsql-patches mailing list (pgsql-patches@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches |
|
|
Re: review: table function support
by Pavel Stehule
::
Rate this Message:
2008/7/10 Marko Kreen <markokr@...>:
> On 7/10/08, Pavel Stehule <pavel.stehule@...> wrote: >> I am sending actualized patch >> >> Regards >> Pavel Stehule >> >> 2008/7/9 Pavel Stehule <pavel.stehule@...>: >> >> > 2008/7/9 Marko Kreen <markokr@...>: >> >> 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. > Regards and thank you very much Pavel > -- > marko > [tabfce1.2.diff] *** ./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 (pgsql-patches@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches |
|
|
Re: review: table function support
by Bruce Momjian-5
::
Rate this Message:
Added to September commit fest. --------------------------------------------------------------------------- Pavel Stehule wrote: > 2008/7/10 Marko Kreen <markokr@...>: > > On 7/10/08, Pavel Stehule <pavel.stehule@...> wrote: > >> I am sending actualized patch > >> > >> Regards > >> Pavel Stehule > >> > >> 2008/7/9 Pavel Stehule <pavel.stehule@...>: > >> > >> > 2008/7/9 Marko Kreen <markokr@...>: > >> >> 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 > > [ Attachment, skipping... ] > > -- > Sent via pgsql-patches mailing list (pgsql-patches@...) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-patches -- Bruce Momjian <bruce@...> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches |
|
|
|
| Free embeddable forum powered by Nabble | Forum Help |
