Re: AW: Re: Support for %TYPE in CREATE FUNCTION - Mailing list pgsql-hackers

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

> > >     AND  expect  it  to  do  more  than that.  So a NOTICE at the
> > >     actual usage, telling that  x%TYPE  for  y  got  resolved  to
> > >     basetype  z  and will currently NOT follow later changes to x
> > >     should do it.
> >
> > So if you could implement it like that, we will be VERY happy.
>
> I also like that approach.

Well, if it helps, here is the patch again, with the NOTICE.

Ian

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.23
diff -u -p -r1.23 create_function.sgml
--- doc/src/sgml/ref/create_function.sgml    2001/05/19 09:01:10    1.23
+++ doc/src/sgml/ref/create_function.sgml    2001/06/01 16:52:56
@@ -55,10 +55,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
-       <literal>opaque</literal>.  <literal>Opaque</literal> indicates
+       input types may be base or complex types,
+       <literal>opaque</literal>, 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>
@@ -69,8 +75,10 @@ CREATE FUNCTION <replaceable class="para
      <listitem>
       <para>
        The return data type.  The output type may be specified as a
-       base type, complex type, <literal>setof</literal> type, or
-       <literal>opaque</literal>.  The <literal>setof</literal>
+       base type, complex type, <literal>setof</literal> type,
+       <literal>opaque</literal>, or the same as the type of an
+       existing column.
+       The <literal>setof</literal>
        modifier indicates that the function will return a set of
        items, rather than a single item.  Functions with a declared
        return type of <literal>opaque</literal> do not return a value.
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.187
diff -u -p -r1.187 analyze.c
--- src/backend/parser/analyze.c    2001/05/22 16:37:15    1.187
+++ src/backend/parser/analyze.c    2001/06/01 16:52:58
@@ -29,6 +29,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"
@@ -51,7 +52,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, Oid *pktypoid);
@@ -232,6 +236,17 @@ 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:

@@ -2686,6 +2701,107 @@ 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;
+    char   *tyn;
+
+    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;
+    tyn = typeidTypeName(v->vartype);
+    elog(NOTICE, "%s.%s%%TYPE converted to %s", tn->name, tn->attrname, tyn);
+    tn->name = tyn;
+    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.227
diff -u -p -r2.227 gram.y
--- src/backend/parser/gram.y    2001/05/27 09:59:29    2.227
+++ src/backend/parser/gram.y    2001/06/01 16:53:02
@@ -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

@@ -2490,7 +2490,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.
@@ -2498,7 +2498,7 @@ func_arg:  opt_arg Typename
                      */
                     $$ = $2;
                 }
-        | Typename
+        | func_type
                 {
                     $$ = $1;
                 }
@@ -2526,7 +2526,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.
@@ -2536,6 +2536,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.96
diff -u -p -r1.96 parse_expr.c
--- src/backend/parser/parse_expr.c    2001/05/21 18:42:08    1.96
+++ src/backend/parser/parse_expr.c    2001/06/01 16:53:03
@@ -942,6 +942,7 @@ parser_typecast_expression(ParseState *p
 char *
 TypeNameToInternalName(TypeName *typename)
 {
+    Assert(typename->attrname == NULL);
     if (typename->arrayBounds != NIL)
     {

Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.129
diff -u -p -r1.129 parsenodes.h
--- src/include/nodes/parsenodes.h    2001/05/21 18:42:08    1.129
+++ src/include/nodes/parsenodes.h    2001/06/01 16:53:09
@@ -951,6 +951,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/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 -u -p -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/06/01 16:53:18
@@ -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 -u -p -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/06/01 16:53:18
@@ -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 -u -p -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/06/01 16:53:18
@@ -9,6 +9,12 @@ 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';
+NOTICE:  hobbies_r.name%TYPE converted to text
+NOTICE:  hobbies_r.person%TYPE converted to text
 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 -u -p -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/06/01 16:53:18
@@ -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
 --