Horton
Menu

Thread

  1. AW: Re: Support for %TYPE in CREATE FUNCTION

    Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2001-06-01T07:55:54Z

    > >     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.

Andreas

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

    Ian Lance Taylor <ian@airs.com> — 2001-06-01T16:59:07Z

    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
 --

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-02T16:39:41Z

    Where are we on this?  Tom is against it, Jan was initially against it,
and I have counted 4-5 people who want it.


> 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
>  --
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-04T23:25:54Z

    Because several people want this patch, Tom has withdrawn his
objection.  Jan also stated that the elog(NOTICE) was good enough for
him.

Patch applied.

> 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
>  --
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

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

  5. Re: [PATCHES] Re: AW: Re: Support for %TYPE in CREATE FUNCTION

    Pascal Scheffers <pascal@scheffers.net> — 2001-06-05T09:07:03Z

    On Mon, 4 Jun 2001, Bruce Momjian wrote:

> Because several people want this patch, Tom has withdrawn his
> objection.  Jan also stated that the elog(NOTICE) was good enough for
> him.
>
> Patch applied.

Wonderful! Thank you all! Do you have any kind of ETA for when this
feature will be publicly available? Is this going to be included in 7.1.3
or is it 7.2 stuff (just curious)?

Pascal.

  6. Re: Re: [PATCHES] Re: AW: Re: Support for %TYPE in CREATE FUNCTION

    Karel Zak <zakkr@zf.jcu.cz> — 2001-06-05T09:30:41Z

    On Tue, Jun 05, 2001 at 11:07:03AM +0200, Pascal Scheffers wrote:
> On Mon, 4 Jun 2001, Bruce Momjian wrote:
> 
> > Because several people want this patch, Tom has withdrawn his
> > objection.  Jan also stated that the elog(NOTICE) was good enough for
> > him.
> >
> > Patch applied.
> 
> Wonderful! Thank you all! Do you have any kind of ETA for when this
> feature will be publicly available? Is this going to be included in 7.1.3
> or is it 7.2 stuff (just curious)?


 I mean we're in 7.2 cycle -- into 7.1.x go bugfixes only.

                                Karel
-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz