Re: [HACKERS] Support for %TYPE in CREATE FUNCTION

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Ian Lance Taylor <ian(at)airs(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Support for %TYPE in CREATE FUNCTION
Date: 2001-05-28 14:15:23
Message-ID: 200105281415.f4SEFNF04218@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches


Sorry, looks like this patch has to be rejected because it can not
handle table changes.

> This patch adds support for %TYPE in CREATE FUNCTION argument and
> return types.
>
> %TYPE is already supported by PL/pgSQL when declaring variables.
> However, that does not help with the argument and return types in
> CREATE FUNCTION.
>
> Using %TYPE makes it easier to write a function which is independent
> of the definition of a table. That is, minor changes to the types
> used in the table may not require changes to the function.
>
> For example, this trivial function will work whenever `table' which
> has columns named `name' and `value', no matter what the types of the
> columns are.
>
> CREATE FUNCTION lookup (table.name%TYPE)
> RETURNS table.value%TYPE
> AS 'select value from table where name = $1'
> LANGUAGE 'sql';
>
> This patch includes changes to the testsuite and the documentation.
>
> This work was sponsored by Zembu.
>
> Ian
>
> Index: src/include/nodes/parsenodes.h
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
> retrieving revision 1.126
> diff -p -u -r1.126 parsenodes.h
> --- src/include/nodes/parsenodes.h 2001/03/23 04:49:56 1.126
> +++ src/include/nodes/parsenodes.h 2001/04/28 03:38:21
> @@ -945,6 +945,7 @@ typedef struct TypeName
> bool setof; /* is a set? */
> int32 typmod; /* type modifier */
> List *arrayBounds; /* array bounds */
> + char *attrname; /* field name when using %TYPE */
> } TypeName;
>
> /*
> Index: src/backend/parser/analyze.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/analyze.c,v
> retrieving revision 1.183
> diff -p -u -r1.183 analyze.c
> --- src/backend/parser/analyze.c 2001/03/22 06:16:15 1.183
> +++ src/backend/parser/analyze.c 2001/04/28 03:38:23
> @@ -27,6 +27,7 @@
> #include "parser/parse_relation.h"
> #include "parser/parse_target.h"
> #include "parser/parse_type.h"
> +#include "parser/parse_expr.h"
> #include "rewrite/rewriteManip.h"
> #include "utils/builtins.h"
> #include "utils/fmgroids.h"
> @@ -49,7 +50,10 @@ static Node *transformSetOperationTree(P
> static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
> static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt);
> static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt);
> +static Node *transformTypeRefs(ParseState *pstate, Node *stmt);
>
> +static void transformTypeRefsList(ParseState *pstate, List *l);
> +static void transformTypeRef(ParseState *pstate, TypeName *tn);
> static List *getSetColTypes(ParseState *pstate, Node *node);
> static void transformForUpdate(Query *qry, List *forUpdate);
> static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint);
> @@ -230,6 +234,18 @@ transformStmt(ParseState *pstate, Node *
> (SelectStmt *) parseTree);
> break;
>
> + /*
> + * Convert use of %TYPE in statements where it is permitted.
> + */
> + case T_ProcedureStmt:
> + case T_CommentStmt:
> + case T_RemoveFuncStmt:
> + case T_DefineStmt:
> + result = makeNode(Query);
> + result->commandType = CMD_UTILITY;
> + result->utilityStmt = transformTypeRefs(pstate, parseTree);
> + break;
> +
> default:
>
> /*
> @@ -2607,6 +2623,104 @@ transformAlterTableStmt(ParseState *psta
> }
> qry->utilityStmt = (Node *) stmt;
> return qry;
> +}
> +
> +/*
> + * Transform uses of %TYPE in a statement.
> + */
> +static Node *
> +transformTypeRefs(ParseState *pstate, Node *stmt)
> +{
> + switch (nodeTag(stmt))
> + {
> + case T_ProcedureStmt:
> + {
> + ProcedureStmt *ps = (ProcedureStmt *) stmt;
> +
> + transformTypeRefsList(pstate, ps->argTypes);
> + transformTypeRef(pstate, (TypeName *) ps->returnType);
> + transformTypeRefsList(pstate, ps->withClause);
> + }
> + break;
> +
> + case T_CommentStmt:
> + {
> + CommentStmt *cs = (CommentStmt *) stmt;
> +
> + transformTypeRefsList(pstate, cs->objlist);
> + }
> + break;
> +
> + case T_RemoveFuncStmt:
> + {
> + RemoveFuncStmt *rs = (RemoveFuncStmt *) stmt;
> +
> + transformTypeRefsList(pstate, rs->args);
> + }
> + break;
> +
> + case T_DefineStmt:
> + {
> + DefineStmt *ds = (DefineStmt *) stmt;
> + List *ele;
> +
> + foreach(ele, ds->definition)
> + {
> + DefElem *de = (DefElem *) lfirst(ele);
> +
> + if (de->arg != NULL
> + && IsA(de->arg, TypeName))
> + {
> + transformTypeRef(pstate, (TypeName *) de->arg);
> + }
> + }
> + }
> + break;
> +
> + default:
> + elog(ERROR, "Unsupported type %d in transformTypeRefs",
> + nodeTag(stmt));
> + break;
> + }
> +
> + return stmt;
> +}
> +
> +/*
> + * Transform uses of %TYPE in a list.
> + */
> +static void
> +transformTypeRefsList(ParseState *pstate, List *l)
> +{
> + List *ele;
> +
> + foreach(ele, l)
> + {
> + if (IsA(lfirst(ele), TypeName))
> + transformTypeRef(pstate, (TypeName *) lfirst(ele));
> + }
> +}
> +
> +/*
> + * Transform a TypeName to not use %TYPE.
> + */
> +static void
> +transformTypeRef(ParseState *pstate, TypeName *tn)
> +{
> + Attr *att;
> + Node *n;
> + Var *v;
> +
> + if (tn->attrname == NULL)
> + return;
> + att = makeAttr(tn->name, tn->attrname);
> + n = transformExpr(pstate, (Node *) att, EXPR_COLUMN_FIRST);
> + if (! IsA(n, Var))
> + elog(ERROR, "unsupported expression in %%TYPE");
> + v = (Var *) n;
> + tn->name = typeidTypeName(v->vartype);
> + tn->typmod = v->vartypmod;
> + tn->attrname = NULL;
> }
>
> /* exported so planner can check again after rewriting, query pullup, etc */
> Index: src/backend/parser/gram.y
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
> retrieving revision 2.221
> diff -p -u -r2.221 gram.y
> --- src/backend/parser/gram.y 2001/02/18 18:06:10 2.221
> +++ src/backend/parser/gram.y 2001/04/28 03:38:26
> @@ -192,7 +192,7 @@ static void doNegateFloat(Value *v);
> def_list, opt_indirection, group_clause, TriggerFuncArgs,
> select_limit, opt_select_limit
>
> -%type <typnam> func_arg, func_return, aggr_argtype
> +%type <typnam> func_arg, func_return, func_type, aggr_argtype
>
> %type <boolean> opt_arg, TriggerForOpt, TriggerForType, OptTemp
>
> @@ -2462,7 +2462,7 @@ func_args_list: func_arg
> { $$ = lappend($1, $3); }
> ;
>
> -func_arg: opt_arg Typename
> +func_arg: opt_arg func_type
> {
> /* We can catch over-specified arguments here if we want to,
> * but for now better to silently swallow typmod, etc.
> @@ -2470,7 +2470,7 @@ func_arg: opt_arg Typename
> */
> $$ = $2;
> }
> - | Typename
> + | func_type
> {
> $$ = $1;
> }
> @@ -2498,7 +2498,7 @@ func_as: Sconst
> { $$ = makeList2(makeString($1), makeString($3)); }
> ;
>
> -func_return: Typename
> +func_return: func_type
> {
> /* We can catch over-specified arguments here if we want to,
> * but for now better to silently swallow typmod, etc.
> @@ -2508,6 +2508,18 @@ func_return: Typename
> }
> ;
>
> +func_type: Typename
> + {
> + $$ = $1;
> + }
> + | IDENT '.' ColId '%' TYPE_P
> + {
> + $$ = makeNode(TypeName);
> + $$->name = $1;
> + $$->typmod = -1;
> + $$->attrname = $3;
> + }
> + ;
>
> /*****************************************************************************
> *
> Index: src/backend/parser/parse_expr.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/parse_expr.c,v
> retrieving revision 1.92
> diff -p -u -r1.92 parse_expr.c
> --- src/backend/parser/parse_expr.c 2001/03/22 03:59:41 1.92
> +++ src/backend/parser/parse_expr.c 2001/04/28 03:38:26
> @@ -939,6 +939,7 @@ parser_typecast_expression(ParseState *p
> char *
> TypeNameToInternalName(TypeName *typename)
> {
> + Assert(typename->attrname == NULL);
> if (typename->arrayBounds != NIL)
> {
>
> Index: src/test/regress/input/create_function_2.source
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/input/create_function_2.source,v
> retrieving revision 1.12
> diff -p -u -r1.12 create_function_2.source
> --- src/test/regress/input/create_function_2.source 2000/11/20 20:36:54 1.12
> +++ src/test/regress/input/create_function_2.source 2001/04/28 03:38:27
> @@ -13,6 +13,12 @@ CREATE FUNCTION hobby_construct(text, te
> LANGUAGE 'sql';
>
>
> +CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
> + RETURNS hobbies_r.person%TYPE
> + AS 'select person from hobbies_r where name = $1'
> + LANGUAGE 'sql';
> +
> +
> CREATE FUNCTION equipment(hobbies_r)
> RETURNS setof equipment_r
> AS 'select * from equipment_r where hobby = $1.name'
> Index: src/test/regress/input/misc.source
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/input/misc.source,v
> retrieving revision 1.14
> diff -p -u -r1.14 misc.source
> --- src/test/regress/input/misc.source 2000/11/20 20:36:54 1.14
> +++ src/test/regress/input/misc.source 2001/04/28 03:38:28
> @@ -214,6 +214,7 @@ SELECT user_relns() AS user_relns
>
> --SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
>
> +SELECT hobbies_by_name('basketball');
>
> --
> -- check that old-style C functions work properly with TOASTed values
> Index: src/test/regress/output/create_function_2.source
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/output/create_function_2.source,v
> retrieving revision 1.13
> diff -p -u -r1.13 create_function_2.source
> --- src/test/regress/output/create_function_2.source 2000/11/20 20:36:54 1.13
> +++ src/test/regress/output/create_function_2.source 2001/04/28 03:38:28
> @@ -9,6 +9,10 @@ CREATE FUNCTION hobby_construct(text, te
> RETURNS hobbies_r
> AS 'select $1 as name, $2 as hobby'
> LANGUAGE 'sql';
> +CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
> + RETURNS hobbies_r.person%TYPE
> + AS 'select person from hobbies_r where name = $1'
> + LANGUAGE 'sql';
> CREATE FUNCTION equipment(hobbies_r)
> RETURNS setof equipment_r
> AS 'select * from equipment_r where hobby = $1.name'
> Index: src/test/regress/output/misc.source
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/output/misc.source,v
> retrieving revision 1.27
> diff -p -u -r1.27 misc.source
> --- src/test/regress/output/misc.source 2000/11/20 20:36:54 1.27
> +++ src/test/regress/output/misc.source 2001/04/28 03:38:28
> @@ -656,6 +656,12 @@ SELECT user_relns() AS user_relns
> (90 rows)
>
> --SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name;
> +SELECT hobbies_by_name('basketball');
> + hobbies_by_name
> +-----------------
> + joe
> +(1 row)
> +
> --
> -- check that old-style C functions work properly with TOASTed values
> --
> Index: doc/src/sgml/ref/create_function.sgml
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v
> retrieving revision 1.21
> diff -p -u -r1.21 create_function.sgml
> --- doc/src/sgml/ref/create_function.sgml 2000/12/25 23:15:26 1.21
> +++ doc/src/sgml/ref/create_function.sgml 2001/04/28 03:38:31
> @@ -58,10 +58,16 @@ CREATE FUNCTION <replaceable class="para
> <listitem>
> <para>
> The data type(s) of the function's arguments, if any.
> - The input types may be base or complex types, or
> - <firstterm>opaque</firstterm>.
> + The input types may be base or complex types,
> + <firstterm>opaque</firstterm>, or the same as the type of an
> + existing column.
> <literal>Opaque</literal> indicates that the function
> accepts arguments of a non-SQL type such as <type>char *</type>.
> + The type of a column is indicated using <replaceable
> + class="parameter">tablename</replaceable>.<replaceable
> + class="parameter">columnname</replaceable><literal>%TYPE</literal>;
> + using this can sometimes help make a function independent from
> + changes to the definition of a table.
> </para>
> </listitem>
> </varlistentry>
> @@ -72,7 +78,8 @@ CREATE FUNCTION <replaceable class="para
> The return data type.
> The output type may be specified as a base type, complex type,
> <option>setof type</option>,
> - or <option>opaque</option>.
> + <option>opaque</option>, or the same as the type of an
> + existing column.
> The <option>setof</option>
> modifier indicates that the function will return a set of items,
> rather than a single item.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message bpalmer 2001-05-28 14:58:16 7.1.2?
Previous Message Bruce Momjian 2001-05-28 14:14:55 Re: Re: [HACKERS] Outstanding patches

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2001-05-28 19:17:32 Patch to remove sort files, temp tables, unreferenced files
Previous Message Bruce Momjian 2001-05-28 13:49:12 Re: libpq performance issue