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

Reply via email to