Skip site navigation (1) Skip section navigation (2)

Re: Re: AW: [HACKERS] Re: 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: Re: AW: [HACKERS] Re: Support for %TYPE in CREATE FUNCTION
Date: 2001-06-04 23:25:54
Message-ID: 200106042325.f54NPsJ20372@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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(at)wien(dot)spardat(dot)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(at)postgresql(dot)org)
> 

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

pgsql-hackers by date

Next:From: Tom LaneDate: 2001-06-04 23:58:26
Subject: Re: Curious (mis)behavior of access rights
Previous:From: Matthew T. O'ConnorDate: 2001-06-04 23:16:21
Subject: Re: Curious (mis)behavior of access rights

pgsql-patches by date

Next:From: Pascal ScheffersDate: 2001-06-05 09:07:03
Subject: Re: [PATCHES] Re: AW: Re: Support for %TYPE in CREATE FUNCTION
Previous:From: Bruce MomjianDate: 2001-06-04 21:31:04
Subject: Re: take 2: show all / reset all

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group