Re: [HACKERS] SQL99 ARRAY support proposal

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Hmm. I don't see why we should drag ANY into this --- it should just be
> > a no-constraints placeholder, same as before. What's the gain from
> > constraining it that you don't get from ANYELEMENT?
>
> [...snip...]
>
> >> XXX should this case be rejected at the point of function creation?
> >
> > Probably. This case could be handled just as well by declaring the
> > output to be ANY, I'd think.
>
> [...snip...]
>
> > Likewise. The point of (this reinterpretation of) ANYARRAY and
> > ANYELEMENT is to let the parser deduce the actual output type.
> > If it's not going to be able to deduce anything, use ANY instead.
>
> Here's a new patch with the above corrections. I'm sending it to patches
> in hopes it can be applied now rather than waiting. I think it stands
> alone (shy some documentation, but I'm good for that ;-)) and makes
> sense regardless of the other array support issues.
>
> Thanks,
>
> Joe

> Index: src/backend/catalog/pg_proc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/catalog/pg_proc.c,v
> retrieving revision 1.95
> diff -c -r1.95 pg_proc.c
> *** src/backend/catalog/pg_proc.c 12 Dec 2002 15:49:24 -0000 1.95
> --- src/backend/catalog/pg_proc.c 13 Mar 2003 01:39:59 -0000
> ***************
> *** 86,91 ****
> --- 86,114 ----
> elog(ERROR, "functions cannot have more than %d arguments",
> FUNC_MAX_ARGS);
>
> + /*
> + * Do not allow return type ANYARRAY or ANYELEMENT unless at least one
> + * argument is also ANYARRAY or ANYELEMENT
> + */
> + if (returnType == ANYARRAYOID || returnType == ANYELEMENTOID)
> + {
> + bool genericParam = false;
> +
> + for (i = 0; i < parameterCount; i++)
> + {
> + if (parameterTypes[i] == ANYARRAYOID ||
> + parameterTypes[i] == ANYELEMENTOID)
> + {
> + genericParam = true;
> + break;
> + }
> + }
> +
> + if (!genericParam)
> + elog(ERROR, "functions returning ANYARRAY or ANYELEMENT must " \
> + "have at least one argument of either type");
> + }
> +
> /* Make sure we have a zero-padded param type array */
> MemSet(typev, 0, FUNC_MAX_ARGS * sizeof(Oid));
> if (parameterCount > 0)
> Index: src/backend/parser/parse_coerce.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_coerce.c,v
> retrieving revision 2.93
> diff -c -r2.93 parse_coerce.c
> *** src/backend/parser/parse_coerce.c 9 Feb 2003 06:56:28 -0000 2.93
> --- src/backend/parser/parse_coerce.c 13 Mar 2003 01:39:59 -0000
> ***************
> *** 188,194 ****
>
> ReleaseSysCache(targetType);
> }
> ! else if (targetTypeId == ANYOID ||
> targetTypeId == ANYARRAYOID)
> {
> /* assume can_coerce_type verified that implicit coercion is okay */
> --- 188,194 ----
>
> ReleaseSysCache(targetType);
> }
> ! else if (targetTypeId == ANYOID || targetTypeId == ANYELEMENTOID ||
> targetTypeId == ANYARRAYOID)
> {
> /* assume can_coerce_type verified that implicit coercion is okay */
> ***************
> *** 325,332 ****
> continue;
> }
>
> ! /* accept if target is ANY */
> ! if (targetTypeId == ANYOID)
> continue;
>
> /*
> --- 325,332 ----
> continue;
> }
>
> ! /* accept if target is ANY or ANYELEMENT */
> ! if (targetTypeId == ANYOID || targetTypeId == ANYELEMENTOID)
> continue;
>
> /*
> Index: src/backend/parser/parse_func.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_func.c,v
> retrieving revision 1.144
> diff -c -r1.144 parse_func.c
> *** src/backend/parser/parse_func.c 9 Feb 2003 06:56:28 -0000 1.144
> --- src/backend/parser/parse_func.c 13 Mar 2003 01:39:59 -0000
> ***************
> *** 41,46 ****
> --- 41,50 ----
> List *fargs,
> Oid *input_typeids,
> Oid *function_typeids);
> + static Oid enforce_generic_type_consistency(Oid *oid_array,
> + Oid *true_oid_array,
> + int nargs,
> + Oid rettype);
> static int match_argtypes(int nargs,
> Oid *input_typeids,
> FuncCandidateList function_typeids,
> ***************
> *** 309,314 ****
> --- 313,324 ----
> "\n\tYou may need to add explicit typecasts");
> }
>
> + /*
> + * enforce consistency with ANYARRAY and ANYELEMENT argument
> + * and return types, possibly modifying return type along the way
> + */
> + rettype = enforce_generic_type_consistency(oid_array, true_oid_array, nargs, rettype);
> +
> /* perform the necessary typecasting of arguments */
> make_arguments(nargs, fargs, oid_array, true_oid_array);
>
> ***************
> *** 347,352 ****
> --- 357,466 ----
> return retval;
> }
>
> + /*
> + * If ANYARRAY or ANYELEMENT is used for a function's arguments or
> + * return type, make sure the runtime types are consistent with
> + * each other. The argument consistency rules are like so:
> + *
> + * 1) All arguments declared ANYARRAY should have matching datatypes.
> + * 2) All arguments declared ANYELEMENT should have matching datatypes.
> + * 3) If there are arguments of both ANYELEMENT and ANYARRAY, make sure
> + * the runtime scalar argument type is in fact the element type for
> + * the runtime array argument type.
> + *
> + * Rules are applied to the function's return type (possibly altering it)
> + * if it is declared ANYARRAY or ANYELEMENT:
> + *
> + * 1) If return type is ANYARRAY, and any argument is ANYARRAY, use the
> + * arguments runtime type as the function's return type.
> + * 2) If return type is ANYARRAY, no argument is ANYARRAY, but any argument
> + * is ANYELEMENT, use the runtime type of the argument to determine
> + * the function's return type, i.e. the element type's corresponding
> + * array type.
> + * 3) If return type is ANYARRAY, no argument is ANYARRAY or ANYELEMENT,
> + * generate an ERROR. This condition is prevented by CREATE FUNCTION
> + * and is therefore unexpected here.
> + * 4) If return type is ANYELEMENT, and any argument is ANYELEMENT, use the
> + * arguments runtime type as the function's return type.
> + * 5) If return type is ANYELEMENT, no argument is ANYELEMENT, but any argument
> + * is ANYARRAY, use the runtime type of the argument to determine
> + * the function's return type, i.e. the array type's corresponding
> + * element type.
> + * 6) If return type is ANYELEMENT, no argument is ANYARRAY or ANYELEMENT,
> + * generate an ERROR. This condition is prevented by CREATE FUNCTION
> + * and is therefore unexpected here.
> + */
> + static Oid
> + enforce_generic_type_consistency(Oid *oid_array, Oid *true_oid_array, int nargs, Oid rettype)
> + {
> + int j;
> + Oid elem_typeid = InvalidOid;
> + Oid array_typeid = InvalidOid;
> + Oid array_typelem = InvalidOid;
> +
> + /*
> + * Loop through the arguments to see if we have any that are
> + * ANYARRAY or ANYELEMENT. If so, require the runtime types to be
> + * self-consistent
> + */
> + for (j = 0; j < nargs; j++)
> + {
> + if (true_oid_array[j] == ANYELEMENTOID)
> + {
> + if (OidIsValid(elem_typeid) && oid_array[j] != elem_typeid)
> + elog(ERROR, "Inconsistent use of arguments declared ANYELEMENT");
> + elem_typeid = oid_array[j];
> + }
> +
> + if (true_oid_array[j] == ANYARRAYOID)
> + {
> + if (OidIsValid(array_typeid) && oid_array[j] != array_typeid)
> + elog(ERROR, "Inconsistent use of arguments declared ANYARRAY");
> + array_typeid = oid_array[j];
> + }
> + }
> +
> + /*
> + * Fast Track: if none of the arguments are ANYARRAY or ANYELEMENT,
> + * return the original rettype now
> + */
> + if (!OidIsValid(array_typeid) && !OidIsValid(elem_typeid))
> + return rettype;
> +
> + /* get the element type based on the array type, if we have one */
> + if (OidIsValid(array_typeid))
> + {
> + array_typelem = get_typelem(array_typeid);
> +
> + if (!OidIsValid(elem_typeid))
> + {
> + /* if we don't have an element type yet, use the one we just got */
> + elem_typeid = array_typelem;
> + }
> + else if (array_typelem != elem_typeid)
> + {
> + /* otherwise, they better match */
> + elog(ERROR, "Argument declared ANYARRAY not consistent with " \
> + "argument declared ANYELEMENT");
> + }
> + }
> +
> + /* if we return ANYARRAYOID enforce consistency with any like arguments */
> + if (rettype == ANYARRAYOID)
> + {
> + if (OidIsValid(array_typeid))
> + return array_typeid;
> + else
> + return get_arraytype(elem_typeid);
> + }
> +
> + /* if we return ANYELEMENTOID check consistency with any like arguments */
> + if (rettype == ANYELEMENTOID)
> + return elem_typeid;
> +
> + /* we don't return a generic type; send back the original return type */
> + return rettype;
> + }
>
> /* match_argtypes()
> *
> Index: src/backend/utils/cache/lsyscache.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/cache/lsyscache.c,v
> retrieving revision 1.90
> diff -c -r1.90 lsyscache.c
> *** src/backend/utils/cache/lsyscache.c 3 Feb 2003 21:15:44 -0000 1.90
> --- src/backend/utils/cache/lsyscache.c 13 Mar 2003 01:39:59 -0000
> ***************
> *** 1040,1045 ****
> --- 1040,1108 ----
> }
>
> /*
> + * get_typelem
> + *
> + * Given the type OID, return the typelem field (element type OID
> + * for array types)
> + */
> + Oid
> + get_typelem(Oid typid)
> + {
> + HeapTuple tp;
> +
> + tp = SearchSysCache(TYPEOID,
> + ObjectIdGetDatum(typid),
> + 0, 0, 0);
> + if (HeapTupleIsValid(tp))
> + {
> + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
> + Oid result;
> +
> + result = typtup->typelem;
> + ReleaseSysCache(tp);
> + return result;
> + }
> + else
> + return InvalidOid;
> + }
> +
> + /*
> + * get_arraytype
> + *
> + * Given an element type OID, return the OID the corresponding
> + * array type
> + */
> + Oid
> + get_arraytype(Oid elem_typeid)
> + {
> + HeapTuple tp;
> +
> + tp = SearchSysCache(TYPEOID,
> + ObjectIdGetDatum(elem_typeid),
> + 0, 0, 0);
> + if (HeapTupleIsValid(tp))
> + {
> + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
> + char *elem_typename;
> + Oid elem_namespaceId;
> + char *array_typename;
> +
> + elem_typename = pstrdup(NameStr(typtup->typname));
> + elem_namespaceId = typtup->typnamespace;
> + ReleaseSysCache(tp);
> +
> + array_typename = makeArrayTypeName(elem_typename);
> +
> + return GetSysCacheOid(TYPENAMENSP,
> + PointerGetDatum(array_typename),
> + ObjectIdGetDatum(elem_namespaceId),
> + 0, 0);
> + }
> + else
> + return InvalidOid;
> + }
> +
> + /*
> * get_typdefault
> * Given a type OID, return the type's default value, if any.
> *
> Index: src/include/catalog/pg_type.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/catalog/pg_type.h,v
> retrieving revision 1.139
> diff -c -r1.139 pg_type.h
> *** src/include/catalog/pg_type.h 23 Jan 2003 23:39:06 -0000 1.139
> --- src/include/catalog/pg_type.h 13 Mar 2003 01:39:59 -0000
> ***************
> *** 523,528 ****
> --- 523,530 ----
> #define ANYOID 2276
> DATA(insert OID = 2277 ( anyarray PGNSP PGUID -1 f p t \054 0 0 anyarray_in anyarray_out i x f 0 -1 0 _null_ _null_ ));
> #define ANYARRAYOID 2277
> + DATA(insert OID = 2283 ( anyelement PGNSP PGUID 4 t p t \054 0 0 any_in any_out i p f 0 -1 0 _null_ _null_ ));
> + #define ANYELEMENTOID 2283
> DATA(insert OID = 2278 ( void PGNSP PGUID 4 t p t \054 0 0 void_in void_out i p f 0 -1 0 _null_ _null_ ));
> #define VOIDOID 2278
> DATA(insert OID = 2279 ( trigger PGNSP PGUID 4 t p t \054 0 0 trigger_in trigger_out i p f 0 -1 0 _null_ _null_ ));
> Index: src/include/utils/lsyscache.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/utils/lsyscache.h,v
> retrieving revision 1.67
> diff -c -r1.67 lsyscache.h
> *** src/include/utils/lsyscache.h 3 Feb 2003 21:15:45 -0000 1.67
> --- src/include/utils/lsyscache.h 13 Mar 2003 01:39:59 -0000
> ***************
> *** 55,60 ****
> --- 55,62 ----
> char *typalign);
> extern char get_typstorage(Oid typid);
> extern int32 get_typtypmod(Oid typid);
> + extern Oid get_typelem(Oid typid);
> + extern Oid get_arraytype(Oid elem_typeid);
> extern Node *get_typdefault(Oid typid);
> extern char get_typtype(Oid typid);
> extern Oid get_typ_typrelid(Oid typid);
> Index: src/test/regress/expected/type_sanity.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/type_sanity.out,v
> retrieving revision 1.17
> diff -c -r1.17 type_sanity.out
> *** src/test/regress/expected/type_sanity.out 15 Jan 2003 19:35:48 -0000 1.17
> --- src/test/regress/expected/type_sanity.out 13 Mar 2003 01:39:59 -0000
> ***************
> *** 101,119 ****
> -----+---------+-----+---------
> (0 rows)
>
> ! -- As of 7.3, this check finds SET and refcursor, which are borrowing
> ! -- other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> (p1.typelem != 0 AND p1.typlen < 0) AND NOT
> (p2.prorettype = p1.oid AND NOT p2.proretset)
> ORDER BY 1;
> ! oid | typname | oid | proname
> ! ------+-----------+-----+-----------
> ! 32 | SET | 109 | unknownin
> ! 1790 | refcursor | 46 | textin
> ! (2 rows)
>
> -- Varlena array types will point to array_in
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> --- 101,120 ----
> -----+---------+-----+---------
> (0 rows)
>
> ! -- As of 7.4, this check finds SET, refcursor, and anyelement, which are
> ! -- borrowing other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> (p1.typelem != 0 AND p1.typlen < 0) AND NOT
> (p2.prorettype = p1.oid AND NOT p2.proretset)
> ORDER BY 1;
> ! oid | typname | oid | proname
> ! ------+------------+------+-----------
> ! 32 | SET | 109 | unknownin
> ! 1790 | refcursor | 46 | textin
> ! 2283 | anyelement | 2294 | any_in
> ! (3 rows)
>
> -- Varlena array types will point to array_in
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> ***************
> *** 126,133 ****
> (0 rows)
>
> -- Check for bogus typoutput routines
> ! -- As of 7.3, this check finds SET and refcursor, which are borrowing
> ! -- other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> --- 127,134 ----
> (0 rows)
>
> -- Check for bogus typoutput routines
> ! -- As of 7.4, this check finds SET, refcursor, and anyelement, which are
> ! -- borrowing other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> ***************
> *** 135,145 ****
> (p2.oid = 'array_out'::regproc AND
> p1.typelem != 0 AND p1.typlen = -1))
> ORDER BY 1;
> ! oid | typname | oid | proname
> ! ------+-----------+-----+------------
> ! 32 | SET | 110 | unknownout
> ! 1790 | refcursor | 47 | textout
> ! (2 rows)
>
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> --- 136,147 ----
> (p2.oid = 'array_out'::regproc AND
> p1.typelem != 0 AND p1.typlen = -1))
> ORDER BY 1;
> ! oid | typname | oid | proname
> ! ------+------------+------+------------
> ! 32 | SET | 110 | unknownout
> ! 1790 | refcursor | 47 | textout
> ! 2283 | anyelement | 2295 | any_out
> ! (3 rows)
>
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> Index: src/test/regress/sql/type_sanity.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/type_sanity.sql,v
> retrieving revision 1.17
> diff -c -r1.17 type_sanity.sql
> *** src/test/regress/sql/type_sanity.sql 15 Jan 2003 19:35:48 -0000 1.17
> --- src/test/regress/sql/type_sanity.sql 13 Mar 2003 01:39:59 -0000
> ***************
> *** 84,91 ****
> p2.proargtypes[1] = 'oid'::regtype AND
> p2.proargtypes[2] = 'int4'::regtype));
>
> ! -- As of 7.3, this check finds SET and refcursor, which are borrowing
> ! -- other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> --- 84,91 ----
> p2.proargtypes[1] = 'oid'::regtype AND
> p2.proargtypes[2] = 'int4'::regtype));
>
> ! -- As of 7.4, this check finds SET, refcursor, and anyelement, which are
> ! -- borrowing other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> ***************
> *** 102,109 ****
>
> -- Check for bogus typoutput routines
>
> ! -- As of 7.3, this check finds SET and refcursor, which are borrowing
> ! -- other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
> --- 102,109 ----
>
> -- Check for bogus typoutput routines
>
> ! -- As of 7.4, this check finds SET, refcursor, and anyelement, which are
> ! -- borrowing other types' I/O routines
> SELECT p1.oid, p1.typname, p2.oid, p2.proname
> FROM pg_type AS p1, pg_proc AS p2
> WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073