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

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

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

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

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

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.

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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz