If I were you I'd file this on the to-fix-later list and concentrate on polymorphic aggregates during the next couple days. If that's not done by Tuesday it will be a tough sell to put in during beta.
Attached is a patch that implements polymorphic aggregates.
Included in the patch, I changed SQL language functions so that they could be declared with and use polymorphic types. This was necessary to facilitate my testing, and I had wanted to implement that all along anyway (in fact I'd still like to allow PL/pgSQL to use polymorphic types, but I'll try to do that separately).
The attached compiles cleanly and passes all regression tests. I've also attached a test script and its output that I used to verify appropriate behavior of CREATE AGGREGATE. The script attempts to cover all possible combinations of inputs and outputs (wrt polymorphic vs non-polymorphic). As far as I can see, the behaviors look correct.
Not sure if it makes sense to do so, but I could either add the test script to an existing regression test, or create a new one with it if desired.
If there are no objections, please apply.
Thanks,
Joe
Index: src/backend/catalog/pg_aggregate.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/catalog/pg_aggregate.c,v
retrieving revision 1.58
diff -c -r1.58 pg_aggregate.c
*** src/backend/catalog/pg_aggregate.c 25 Jun 2003 21:30:25 -0000 1.58
--- src/backend/catalog/pg_aggregate.c 29 Jun 2003 19:17:47 -0000
***************
*** 50,59 ****
Oid finalfn = InvalidOid; /* can be omitted */
Oid finaltype;
Oid fnArgs[FUNC_MAX_ARGS];
! int nargs;
Oid procOid;
TupleDesc tupDesc;
int i;
ObjectAddress myself,
referenced;
--- 50,65 ----
Oid finalfn = InvalidOid; /* can be omitted */
Oid finaltype;
Oid fnArgs[FUNC_MAX_ARGS];
! int nargs_transfn;
! int nargs_finalfn;
Oid procOid;
TupleDesc tupDesc;
int i;
+ Oid rettype;
+ Oid *true_oid_array_transfn;
+ Oid *true_oid_array_finalfn;
+ bool retset;
+ FuncDetailCode fdresult;
ObjectAddress myself,
referenced;
***************
*** 68,91 ****
MemSet(fnArgs, 0, FUNC_MAX_ARGS * sizeof(Oid));
fnArgs[0] = aggTransType;
if (aggBaseType == ANYOID)
! nargs = 1;
else
{
fnArgs[1] = aggBaseType;
! nargs = 2;
}
! transfn = LookupFuncName(aggtransfnName, nargs, fnArgs);
if (!OidIsValid(transfn))
! func_error("AggregateCreate", aggtransfnName, nargs, fnArgs, NULL);
tup = SearchSysCache(PROCOID,
ObjectIdGetDatum(transfn),
0, 0, 0);
if (!HeapTupleIsValid(tup))
! func_error("AggregateCreate", aggtransfnName, nargs, fnArgs, NULL);
proc = (Form_pg_proc) GETSTRUCT(tup);
- if (proc->prorettype != aggTransType)
- elog(ERROR, "return type of transition function %s is not %s",
- NameListToString(aggtransfnName), format_type_be(aggTransType));
/*
* If the transfn is strict and the initval is NULL, make sure input
--- 74,137 ----
MemSet(fnArgs, 0, FUNC_MAX_ARGS * sizeof(Oid));
fnArgs[0] = aggTransType;
if (aggBaseType == ANYOID)
! nargs_transfn = 1;
else
{
fnArgs[1] = aggBaseType;
! nargs_transfn = 2;
}
!
! /*
! * func_get_detail looks up the function in the catalogs, does
! * disambiguation for polymorphic functions, handles inheritance, and
! * returns the funcid and type and set or singleton status of the
! * function's return value. it also returns the true argument types
! * to the function.
! */
! fdresult = func_get_detail(aggtransfnName, NIL, nargs_transfn, fnArgs,
! &transfn, &rettype, &retset,
! &true_oid_array_transfn);
!
! /* only valid case is a normal function */
! if (fdresult != FUNCDETAIL_NORMAL)
! func_error("AggregateCreate", aggtransfnName, nargs_transfn, fnArgs,
NULL);
!
if (!OidIsValid(transfn))
! func_error("AggregateCreate", aggtransfnName, nargs_transfn, fnArgs,
NULL);
!
! /*
! * enforce consistency with ANYARRAY and ANYELEMENT argument
! * and return types, possibly modifying return type along the way
! */
! rettype = enforce_generic_type_consistency(fnArgs, true_oid_array_transfn,
!
nargs_transfn, rettype);
!
! /*
! * func_get_detail will find functions requiring argument type coercion,
! * but we aren't prepared to deal with that
! */
! if (true_oid_array_transfn[0] != ANYARRAYOID &&
! true_oid_array_transfn[0] != ANYELEMENTOID &&
! !IsBinaryCoercible(fnArgs[0], true_oid_array_transfn[0]))
! func_error("AggregateCreate", aggtransfnName, nargs_transfn, fnArgs,
NULL);
!
! if (nargs_transfn == 2 &&
! true_oid_array_transfn[1] != ANYARRAYOID &&
! true_oid_array_transfn[1] != ANYELEMENTOID &&
! !IsBinaryCoercible(fnArgs[1], true_oid_array_transfn[1]))
! func_error("AggregateCreate", aggtransfnName, nargs_transfn, fnArgs,
NULL);
!
! if (rettype != aggTransType)
! elog(ERROR, "return type of transition function %s is not %s",
! NameListToString(aggtransfnName), format_type_be(aggTransType));
!
tup = SearchSysCache(PROCOID,
ObjectIdGetDatum(transfn),
0, 0, 0);
if (!HeapTupleIsValid(tup))
! func_error("AggregateCreate", aggtransfnName,
! nargs_transfn, fnArgs, NULL);
proc = (Form_pg_proc) GETSTRUCT(tup);
/*
* If the transfn is strict and the initval is NULL, make sure input
***************
*** 105,121 ****
{
MemSet(fnArgs, 0, FUNC_MAX_ARGS * sizeof(Oid));
fnArgs[0] = aggTransType;
! finalfn = LookupFuncName(aggfinalfnName, 1, fnArgs);
if (!OidIsValid(finalfn))
func_error("AggregateCreate", aggfinalfnName, 1, fnArgs, NULL);
! tup = SearchSysCache(PROCOID,
! ObjectIdGetDatum(finalfn),
! 0, 0, 0);
! if (!HeapTupleIsValid(tup))
func_error("AggregateCreate", aggfinalfnName, 1, fnArgs, NULL);
- proc = (Form_pg_proc) GETSTRUCT(tup);
- finaltype = proc->prorettype;
- ReleaseSysCache(tup);
}
else
{
--- 151,185 ----
{
MemSet(fnArgs, 0, FUNC_MAX_ARGS * sizeof(Oid));
fnArgs[0] = aggTransType;
! nargs_finalfn = 1;
!
! fdresult = func_get_detail(aggfinalfnName, NIL, 1, fnArgs,
! &finalfn, &rettype,
&retset,
!
&true_oid_array_finalfn);
!
! /* only valid case is a normal function */
! if (fdresult != FUNCDETAIL_NORMAL)
! func_error("AggregateCreate", aggfinalfnName, 1, fnArgs, NULL);
!
if (!OidIsValid(finalfn))
func_error("AggregateCreate", aggfinalfnName, 1, fnArgs, NULL);
!
! /*
! * enforce consistency with ANYARRAY and ANYELEMENT argument
! * and return types, possibly modifying return type along the way
! */
! finaltype = enforce_generic_type_consistency(fnArgs,
!
true_oid_array_finalfn,
!
nargs_finalfn, rettype);
!
! /*
! * func_get_detail will find functions requiring argument type
coercion,
! * but we aren't prepared to deal with that
! */
! if (true_oid_array_finalfn[0] != ANYARRAYOID &&
! true_oid_array_finalfn[0] != ANYELEMENTOID &&
! !IsBinaryCoercible(fnArgs[0], true_oid_array_finalfn[0]))
func_error("AggregateCreate", aggfinalfnName, 1, fnArgs, NULL);
}
else
{
***************
*** 125,130 ****
--- 189,222 ----
finaltype = aggTransType;
}
Assert(OidIsValid(finaltype));
+
+ /*
+ * special disallowed cases:
+ * 1) if finaltype (i.e. aggregate return type) is polymorphic,
+ * basetype must be polymorphic also
+ * 2) if finaltype (i.e. aggregate return type) is non-polymorphic,
+ * and transition function's second argument is non-polymorphic, then
+ * the transition function's first argument may not be polymorphic
+ * unless the state type is non-polymorphic
+ */
+ if ((finaltype == ANYARRAYOID ||
+ finaltype == ANYELEMENTOID) &&
+ (aggBaseType != ANYARRAYOID &&
+ aggBaseType != ANYELEMENTOID))
+ elog(ERROR, "an aggregate returning ANYARRAY or ANYELEMENT " \
+ "must also have either of the them as its base
type");
+
+
+ if ((finaltype != ANYARRAYOID &&
+ finaltype != ANYELEMENTOID) && /* rt
non-poly */
+ (true_oid_array_transfn[0] == ANYARRAYOID ||
+ true_oid_array_transfn[0] == ANYELEMENTOID) && /* tf arg1 poly */
+ (true_oid_array_transfn[1] != ANYARRAYOID &&
+ true_oid_array_transfn[1] != ANYELEMENTOID) && /* tf arg2 non-poly */
+ (aggTransType == ANYARRAYOID ||
+ aggTransType == ANYELEMENTOID)) /* st
arg1 poly */
+ elog(ERROR, "the state function's first argument is ambiguous in a " \
+ "context that cannot support it");
/*
* Everything looks okay. Try to create the pg_proc entry for the
Index: src/backend/catalog/pg_proc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/catalog/pg_proc.c,v
retrieving revision 1.97
diff -c -r1.97 pg_proc.c
*** src/backend/catalog/pg_proc.c 15 Jun 2003 17:59:10 -0000 1.97
--- src/backend/catalog/pg_proc.c 29 Jun 2003 16:26:36 -0000
***************
*** 377,383 ****
typerelid = typeidTypeRelid(rettype);
! if (fn_typtype == 'b' || fn_typtype == 'd')
{
/* Shouldn't have a typerelid */
Assert(typerelid == InvalidOid);
--- 377,386 ----
typerelid = typeidTypeRelid(rettype);
! if (fn_typtype == 'b' ||
! fn_typtype == 'd' ||
! (fn_typtype == 'p' && rettype == ANYARRAYOID) ||
! (fn_typtype == 'p' && rettype == ANYELEMENTOID))
{
/* Shouldn't have a typerelid */
Assert(typerelid == InvalidOid);
***************
*** 595,610 ****
functyptype = get_typtype(proc->prorettype);
/* Disallow pseudotypes in arguments and result */
! /* except that return type can be RECORD or VOID */
if (functyptype == 'p' &&
proc->prorettype != RECORDOID &&
! proc->prorettype != VOIDOID)
elog(ERROR, "SQL functions cannot return type %s",
format_type_be(proc->prorettype));
for (i = 0; i < proc->pronargs; i++)
{
! if (get_typtype(proc->proargtypes[i]) == 'p')
elog(ERROR, "SQL functions cannot have arguments of type %s",
format_type_be(proc->proargtypes[i]));
}
--- 598,617 ----
functyptype = get_typtype(proc->prorettype);
/* Disallow pseudotypes in arguments and result */
! /* except that return type can be RECORD, VOID, ANYARRAY, or ANYELEMENT */
if (functyptype == 'p' &&
proc->prorettype != RECORDOID &&
! proc->prorettype != VOIDOID &&
! proc->prorettype != ANYARRAYOID &&
! proc->prorettype != ANYELEMENTOID)
elog(ERROR, "SQL functions cannot return type %s",
format_type_be(proc->prorettype));
for (i = 0; i < proc->pronargs; i++)
{
! if (get_typtype(proc->proargtypes[i]) == 'p' &&
! proc->proargtypes[i] != ANYARRAYOID &&
! proc->proargtypes[i] != ANYELEMENTOID)
elog(ERROR, "SQL functions cannot have arguments of type %s",
format_type_be(proc->proargtypes[i]));
}
Index: src/backend/commands/aggregatecmds.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/commands/aggregatecmds.c,v
retrieving revision 1.8
diff -c -r1.8 aggregatecmds.c
*** src/backend/commands/aggregatecmds.c 27 Jun 2003 14:45:27 -0000 1.8
--- src/backend/commands/aggregatecmds.c 29 Jun 2003 16:26:36 -0000
***************
*** 120,126 ****
baseTypeId = typenameTypeId(baseType);
transTypeId = typenameTypeId(transType);
! if (get_typtype(transTypeId) == 'p')
elog(ERROR, "Aggregate transition datatype cannot be %s",
format_type_be(transTypeId));
--- 120,128 ----
baseTypeId = typenameTypeId(baseType);
transTypeId = typenameTypeId(transType);
! if (get_typtype(transTypeId) == 'p' &&
! transTypeId != ANYARRAYOID &&
! transTypeId != ANYELEMENTOID)
elog(ERROR, "Aggregate transition datatype cannot be %s",
format_type_be(transTypeId));
Index: src/backend/executor/functions.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/functions.c,v
retrieving revision 1.66
diff -c -r1.66 functions.c
*** src/backend/executor/functions.c 12 Jun 2003 17:29:26 -0000 1.66
--- src/backend/executor/functions.c 29 Jun 2003 16:26:36 -0000
***************
*** 20,25 ****
--- 20,26 ----
#include "executor/execdefs.h"
#include "executor/executor.h"
#include "executor/functions.h"
+ #include "parser/parse_expr.h"
#include "tcop/pquery.h"
#include "tcop/tcopprot.h"
#include "tcop/utility.h"
***************
*** 212,221 ****
if (nargs > 0)
{
argOidVect = (Oid *) palloc(nargs * sizeof(Oid));
! memcpy(argOidVect,
! procedureStruct->proargtypes,
! nargs * sizeof(Oid));
}
else
argOidVect = (Oid *) NULL;
--- 213,235 ----
if (nargs > 0)
{
+ List *p;
+ int argnum = 0;
+
argOidVect = (Oid *) palloc(nargs * sizeof(Oid));
! if (finfo->fn_expr)
! {
! /*
! * If we have a function expression node available to us
! * use it, as any polymorphic types should have been
! * disambiguated for us already
! */
! foreach(p, ((FuncExpr *) finfo->fn_expr)->args)
! argOidVect[argnum++] = exprType((Node *) lfirst(p));
! }
! else
! memcpy(argOidVect, procedureStruct->proargtypes,
!
nargs * sizeof(Oid));
}
else
argOidVect = (Oid *) NULL;
Index: src/backend/executor/nodeAgg.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/executor/nodeAgg.c,v
retrieving revision 1.109
diff -c -r1.109 nodeAgg.c
*** src/backend/executor/nodeAgg.c 25 Jun 2003 21:30:28 -0000 1.109
--- src/backend/executor/nodeAgg.c 29 Jun 2003 16:26:36 -0000
***************
*** 59,64 ****
--- 59,65 ----
#include "executor/nodeAgg.h"
#include "miscadmin.h"
#include "optimizer/clauses.h"
+ #include "parser/parse_agg.h"
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
#include "parser/parse_oper.h"
***************
*** 1187,1193 ****
--- 1188,1199 ----
AclResult aclresult;
Oid transfn_oid,
finalfn_oid;
+ FuncExpr *transfnexpr,
+ *finalfnexpr;
Datum textInitVal;
+ List *fargs;
+ Oid agg_rt_basetype;
+ Oid transfn_arg1_type;
int i;
/* Planner should have assigned aggregate to correct level */
***************
*** 1238,1243 ****
--- 1244,1274 ----
&peraggstate->transtypeLen,
&peraggstate->transtypeByVal);
+ peraggstate->transfn_oid = transfn_oid = aggform->aggtransfn;
+ peraggstate->finalfn_oid = finalfn_oid = aggform->aggfinalfn;
+
+ /* get the runtime aggregate argument type */
+ fargs = aggref->args;
+ agg_rt_basetype = exprType((Node *) nth(0, fargs));
+
+ expand_aggregate(agg_rt_basetype,
+ aggform->aggtranstype,
+ aggref->aggfnoid,
+ transfn_oid,
+ finalfn_oid,
+ &transfnexpr,
+ &finalfnexpr,
+ &transfn_arg1_type);
+
+ fmgr_info(transfn_oid, &peraggstate->transfn);
+ peraggstate->transfn.fn_expr = (Node *) transfnexpr;
+
+ if (OidIsValid(finalfn_oid))
+ {
+ fmgr_info(finalfn_oid, &peraggstate->finalfn);
+ peraggstate->finalfn.fn_expr = (Node *) finalfnexpr;
+ }
+
/*
* initval is potentially null, so don't try to access it as a
* struct field. Must do it the hard way with SysCacheGetAttr.
***************
*** 1250,1263 ****
peraggstate->initValue = (Datum) 0;
else
peraggstate->initValue = GetAggInitVal(textInitVal,
!
aggform->aggtranstype);
!
! peraggstate->transfn_oid = transfn_oid = aggform->aggtransfn;
! peraggstate->finalfn_oid = finalfn_oid = aggform->aggfinalfn;
!
! fmgr_info(transfn_oid, &peraggstate->transfn);
! if (OidIsValid(finalfn_oid))
! fmgr_info(finalfn_oid, &peraggstate->finalfn);
/*
* If the transfn is strict and the initval is NULL, make sure
--- 1281,1287 ----
peraggstate->initValue = (Datum) 0;
else
peraggstate->initValue = GetAggInitVal(textInitVal,
!
transfn_arg1_type);
/*
* If the transfn is strict and the initval is NULL, make sure
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.258
diff -c -r1.258 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 29 Jun 2003 00:33:43 -0000 1.258
--- src/backend/nodes/copyfuncs.c 29 Jun 2003 16:26:36 -0000
***************
*** 728,733 ****
--- 728,734 ----
COPY_SCALAR_FIELD(agglevelsup);
COPY_SCALAR_FIELD(aggstar);
COPY_SCALAR_FIELD(aggdistinct);
+ COPY_NODE_FIELD(args);
return newnode;
}
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.201
diff -c -r1.201 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 29 Jun 2003 00:33:43 -0000 1.201
--- src/backend/nodes/equalfuncs.c 29 Jun 2003 16:26:36 -0000
***************
*** 205,210 ****
--- 205,211 ----
COMPARE_SCALAR_FIELD(agglevelsup);
COMPARE_SCALAR_FIELD(aggstar);
COMPARE_SCALAR_FIELD(aggdistinct);
+ COMPARE_NODE_FIELD(args);
return true;
}
Index: src/backend/nodes/outfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/outfuncs.c,v
retrieving revision 1.211
diff -c -r1.211 outfuncs.c
*** src/backend/nodes/outfuncs.c 29 Jun 2003 00:33:43 -0000 1.211
--- src/backend/nodes/outfuncs.c 29 Jun 2003 16:26:36 -0000
***************
*** 616,621 ****
--- 616,622 ----
WRITE_UINT_FIELD(agglevelsup);
WRITE_BOOL_FIELD(aggstar);
WRITE_BOOL_FIELD(aggdistinct);
+ WRITE_NODE_FIELD(args);
}
static void
Index: src/backend/nodes/readfuncs.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/nodes/readfuncs.c,v
retrieving revision 1.157
diff -c -r1.157 readfuncs.c
*** src/backend/nodes/readfuncs.c 29 Jun 2003 00:33:43 -0000 1.157
--- src/backend/nodes/readfuncs.c 29 Jun 2003 16:26:36 -0000
***************
*** 416,421 ****
--- 416,422 ----
READ_UINT_FIELD(agglevelsup);
READ_BOOL_FIELD(aggstar);
READ_BOOL_FIELD(aggdistinct);
+ READ_NODE_FIELD(args);
READ_DONE();
}
Index: src/backend/optimizer/util/clauses.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/optimizer/util/clauses.c,v
retrieving revision 1.142
diff -c -r1.142 clauses.c
*** src/backend/optimizer/util/clauses.c 29 Jun 2003 00:33:43 -0000 1.142
--- src/backend/optimizer/util/clauses.c 29 Jun 2003 16:26:36 -0000
***************
*** 133,138 ****
--- 133,160 ----
}
/*****************************************************************************
+ * FUNCTION clause functions
+ *****************************************************************************/
+
+ /*
+ * make_funcclause
+ * Creates a function clause given its function info and argument list.
+ */
+ Expr *
+ make_funcclause(Oid funcid, Oid funcresulttype, bool funcretset,
+ CoercionForm funcformat, List
*funcargs)
+ {
+ FuncExpr *expr = makeNode(FuncExpr);
+
+ expr->funcid = funcid;
+ expr->funcresulttype = funcresulttype;
+ expr->funcretset = funcretset;
+ expr->funcformat = funcformat;
+ expr->args = funcargs;
+ return (Expr *) expr;
+ }
+
+ /*****************************************************************************
* NOT clause functions
*****************************************************************************/
Index: src/backend/parser/parse_agg.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_agg.c,v
retrieving revision 1.53
diff -c -r1.53 parse_agg.c
*** src/backend/parser/parse_agg.c 6 Jun 2003 15:04:02 -0000 1.53
--- src/backend/parser/parse_agg.c 29 Jun 2003 16:26:36 -0000
***************
*** 14,25 ****
--- 14,29 ----
*/
#include "postgres.h"
+ #include "catalog/pg_type.h"
+ #include "nodes/params.h"
#include "optimizer/clauses.h"
#include "optimizer/tlist.h"
#include "optimizer/var.h"
#include "parser/parse_agg.h"
+ #include "parser/parse_type.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+ #include "utils/lsyscache.h"
typedef struct
***************
*** 312,314 ****
--- 316,539 ----
return expression_tree_walker(node, check_ungrouped_columns_walker,
(void *) context);
}
+
+ /*
+ * Create function expressions for the transition and final functions
+ * of an aggregate so that they can be attached to the FmgrInfo nodes
+ * of AggStatePerAgg. If we didn't, the functions would not be able to
+ * know what argument and return data types to use for any that are
+ * polymorphic in its definition.
+ */
+ void
+ expand_aggregate(Oid agg_rt_basetype,
+ Oid agg_statetype,
+ Oid agg_fnoid,
+ Oid transfn_oid,
+ Oid finalfn_oid,
+ FuncExpr **transfnexpr,
+ FuncExpr **finalfnexpr,
+ Oid *transfn_arg1_type)
+ {
+ Oid *transfn_arg_types;
+ List *transfn_args = NIL;
+ int transfn_nargs;
+ Oid transfn_ret_type;
+ Oid *finalfn_arg_types = NULL;
+ List *finalfn_args = NIL;
+ Oid finalfn_ret_type = InvalidOid;
+ int finalfn_nargs = 0;
+ Param *arg0;
+ Param *arg1;
+
+ /* get the transition function argument and return types */
+ transfn_ret_type = get_func_rettype(transfn_oid);
+ transfn_arg_types = get_func_argtypes(transfn_oid, &transfn_nargs);
+
+ /* resolve any polymorphic types */
+ if (transfn_nargs == 2)
+ {
+ /* base type was not ANY */
+ if ((transfn_arg_types[0] == ANYARRAYOID ||
+ transfn_arg_types[0] == ANYELEMENTOID) &&
+ (transfn_arg_types[1] == ANYARRAYOID ||
+ transfn_arg_types[1] == ANYELEMENTOID))
+ {
+ /*
+ * If both transfn args are polymorphic, we can
+ * resolve transfn arg 1 using base type as context
+ */
+ transfn_arg_types[0] = resolve_type(transfn_arg_types[0],
+
agg_rt_basetype);
+ }
+ else if ((transfn_arg_types[0] == ANYARRAYOID ||
+ transfn_arg_types[0] == ANYELEMENTOID))
+ {
+ /*
+ * Otherwise, if transfn arg 1 is polymorphic, we can
+ * resolve it using state type as context. This is only
+ * safe because we prevented the situation where both
+ * state type and transfn arg 1 are polymorphic with a
+ * non-polymorphic transfn arg 2, during aggregate creation.
+ */
+ transfn_arg_types[0] = resolve_type(transfn_arg_types[0],
+
agg_statetype);
+ }
+
+ /*
+ * Now, if transfn arg 2 is polymorphic, we can set it to the runtime
+ * base type without further adieu
+ */
+ if (transfn_arg_types[1] == ANYARRAYOID ||
+ transfn_arg_types[1] == ANYELEMENTOID)
+ transfn_arg_types[1] = agg_rt_basetype;
+
+ /*
+ * Build arg list to use on the transfn FuncExpr node. We really
+ * only care that transfn can discover the actual argument types
+ * at runtime using get_fn_expr_argtype()
+ */
+ arg0 = makeNode(Param);
+ arg0->paramkind = PARAM_EXEC;
+ arg0->paramid = -1;
+ arg0->paramtype = transfn_arg_types[0];
+
+ arg1 = makeNode(Param);
+ arg1->paramkind = PARAM_EXEC;
+ arg1->paramid = -1;
+ arg1->paramtype = transfn_arg_types[1];
+
+ transfn_args = makeList2(arg0, arg1);
+
+ /*
+ * the state transition function always returns the same type
+ * as its first argument
+ */
+ if (transfn_ret_type == ANYARRAYOID ||
+ transfn_ret_type == ANYELEMENTOID)
+ transfn_ret_type = transfn_arg_types[0];
+ }
+ else if (transfn_nargs == 1)
+ /*
+ * base type was ANY, therefore the aggregate return type should
+ * be non-polymorphic
+ */
+ {
+ Oid finaltype = get_func_rettype(agg_fnoid);
+
+ /*
+ * this should have been prevented in AggregateCreate,
+ * but check anyway
+ */
+ if (finaltype == ANYARRAYOID || finaltype == ANYELEMENTOID)
+ elog(ERROR, "an aggregate returning ANYARRAY or ANYELEMENT " \
+ "must also have either of the them as
its base type");
+
+ /* see if we have a final function */
+ if (OidIsValid(finalfn_oid))
+ {
+ finalfn_arg_types = get_func_argtypes(finalfn_oid,
&finalfn_nargs);
+ if (finalfn_nargs != 1)
+ elog(ERROR, "final function takes unexpected number " \
+ "of arguments: %d",
finalfn_nargs);
+
+ /*
+ * final function argument is always the same as the state
+ * function return type
+ */
+ if (finalfn_arg_types[0] != ANYARRAYOID &&
+ finalfn_arg_types[0] != ANYELEMENTOID)
+ {
+ /* if it is not ambiguous, use it */
+ transfn_ret_type = finalfn_arg_types[0];
+ }
+ else
+ {
+ /* if it is ambiguous, try to derive it */
+ finalfn_ret_type = finaltype;
+ finalfn_arg_types[0] =
resolve_type(finalfn_arg_types[0],
+
finalfn_ret_type);
+ transfn_ret_type = finalfn_arg_types[0];
+ }
+ }
+ else
+ transfn_ret_type = finaltype;
+
+ transfn_arg_types[0] = resolve_type(transfn_arg_types[0],
+
transfn_ret_type);
+
+ /*
+ * Build arg list to use on the transfn FuncExpr node. We really
+ * only care that transfn can discover the actual argument types
+ * at runtime using get_fn_expr_argtype()
+ */
+ arg0 = makeNode(Param);
+ arg0->paramkind = PARAM_EXEC;
+ arg0->paramid = -1;
+ arg0->paramtype = transfn_arg_types[0];
+
+ transfn_args = makeList1(arg0);
+ }
+ else
+ elog(ERROR, "state transition function takes unexpected number " \
+ "of arguments: %d", transfn_nargs);
+
+ if (OidIsValid(finalfn_oid))
+ {
+ /* get the final function argument and return types */
+ if (finalfn_ret_type == InvalidOid)
+ finalfn_ret_type = get_func_rettype(finalfn_oid);
+
+ if (!finalfn_arg_types)
+ {
+ finalfn_arg_types = get_func_argtypes(finalfn_oid,
&finalfn_nargs);
+ if (finalfn_nargs != 1)
+ elog(ERROR, "final function takes unexpected number " \
+ "of arguments: %d",
finalfn_nargs);
+ }
+
+ /*
+ * final function argument is always the same as the state
+ * function return type, which by now should have been resolved
+ */
+ if (finalfn_arg_types[0] == ANYARRAYOID ||
+ finalfn_arg_types[0] == ANYELEMENTOID)
+ finalfn_arg_types[0] = transfn_ret_type;
+
+ /*
+ * Build arg list to use on the finalfn FuncExpr node. We really
+ * only care that finalfn can discover the actual argument types
+ * at runtime using get_fn_expr_argtype()
+ */
+ arg0 = makeNode(Param);
+ arg0->paramkind = PARAM_EXEC;
+ arg0->paramid = -1;
+ arg0->paramtype = finalfn_arg_types[0];
+
+ finalfn_args = makeList1(arg0);
+
+ finalfn_ret_type = resolve_type(finalfn_ret_type,
+
finalfn_arg_types[0]);
+ }
+
+ *transfnexpr = (FuncExpr *) make_funcclause(transfn_oid,
+
transfn_ret_type,
+
false,
+
COERCE_DONTCARE,
+
transfn_args);
+
+ if (OidIsValid(finalfn_oid))
+ {
+ *finalfnexpr = (FuncExpr *) make_funcclause(finalfn_oid,
+
finalfn_ret_type,
+
false,
+
COERCE_DONTCARE,
+
finalfn_args);
+ }
+
+ /*
+ * we need to return the resolved transfn arg1 type to be used
+ * by GetAggInitVal
+ */
+ *transfn_arg1_type = transfn_arg_types[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.101
diff -c -r2.101 parse_coerce.c
*** src/backend/parser/parse_coerce.c 27 Jun 2003 00:33:25 -0000 2.101
--- src/backend/parser/parse_coerce.c 29 Jun 2003 16:26:36 -0000
***************
*** 859,865 ****
/* Get the element type based on the array type, if we have one */
if (OidIsValid(array_typeid))
{
! array_typelem = get_element_type(array_typeid);
if (!OidIsValid(array_typelem))
elog(ERROR, "Argument declared ANYARRAY is not an array: %s",
format_type_be(array_typeid));
--- 859,869 ----
/* Get the element type based on the array type, if we have one */
if (OidIsValid(array_typeid))
{
! if (array_typeid != ANYARRAYOID)
! array_typelem = get_element_type(array_typeid);
! else
! array_typelem = ANYELEMENTOID;
!
if (!OidIsValid(array_typelem))
elog(ERROR, "Argument declared ANYARRAY is not an array: %s",
format_type_be(array_typeid));
***************
*** 919,925 ****
{
if (!OidIsValid(array_typeid))
{
! array_typeid = get_array_type(elem_typeid);
if (!OidIsValid(array_typeid))
elog(ERROR, "Cannot find array type for datatype %s",
format_type_be(elem_typeid));
--- 923,933 ----
{
if (!OidIsValid(array_typeid))
{
! if (elem_typeid != ANYELEMENTOID)
! array_typeid = get_array_type(elem_typeid);
! else
! array_typeid = ANYARRAYOID;
!
if (!OidIsValid(array_typeid))
elog(ERROR, "Cannot find array type for datatype %s",
format_type_be(elem_typeid));
Index: src/backend/parser/parse_func.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_func.c,v
retrieving revision 1.152
diff -c -r1.152 parse_func.c
*** src/backend/parser/parse_func.c 25 Jun 2003 21:30:31 -0000 1.152
--- src/backend/parser/parse_func.c 29 Jun 2003 16:26:36 -0000
***************
*** 336,341 ****
--- 336,342 ----
aggref->target = lfirst(fargs);
aggref->aggstar = agg_star;
aggref->aggdistinct = agg_distinct;
+ aggref->args = fargs;
/* parse_agg.c does additional aggregate-specific processing */
transformAggregateCall(pstate, aggref);
Index: src/backend/parser/parse_type.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/parser/parse_type.c,v
retrieving revision 1.57
diff -c -r1.57 parse_type.c
*** src/backend/parser/parse_type.c 29 Apr 2003 22:13:10 -0000 1.57
--- src/backend/parser/parse_type.c 29 Jun 2003 16:26:36 -0000
***************
*** 484,486 ****
--- 484,536 ----
pfree(buf.data);
}
+
+ /*
+ * Given a type_to_resolve oid, typically defined at function creation
+ * (e.g. a function argument or return type), and context_type oid,
+ * typically gleaned by the parser as one of the actual arguments
+ * at function call time, derive the runtime type of type_to_resolve.
+ * The intent is to use runtime context to determine what type we should
+ * assign to a polymorphic argument or return type.
+ *
+ * The rules for this resolution are as follows:
+ * 1) if the context type is polymorphic, punt and return type_to_resolve
+ * unchanged
+ * 2) if type_to_resolve is ANYARRAY (polymorphic), then return context_type
+ * if it is already an array type, or get its array type if not
+ * 3) if type_to_resolve is ANYELEMENT (polymorphic), then return context_type
+ * if it is already an elemental type, or get its element type if not
+ * 4) if type_to_resolve is non-polymorphic, return it unchanged
+ */
+ Oid
+ resolve_type(Oid type_to_resolve, Oid context_type)
+ {
+ Oid resolved_type;
+
+ if (context_type == ANYARRAYOID || context_type == ANYELEMENTOID)
+ resolved_type = type_to_resolve;
+ else if (type_to_resolve == ANYARRAYOID)
+ /* any array */
+ {
+ Oid context_type_arraytype = get_array_type(context_type);
+
+ if (context_type_arraytype != InvalidOid)
+ resolved_type = context_type_arraytype;
+ else
+ resolved_type = context_type;
+ }
+ else if (type_to_resolve == ANYELEMENTOID)
+ /* any element */
+ {
+ Oid context_type_elemtype = get_element_type(context_type);
+
+ if (context_type_elemtype != InvalidOid)
+ resolved_type = context_type_elemtype;
+ else
+ resolved_type = context_type;
+ }
+ else
+ resolved_type = type_to_resolve;
+
+ return resolved_type;
+ }
Index: src/backend/utils/cache/lsyscache.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/cache/lsyscache.c,v
retrieving revision 1.100
diff -c -r1.100 lsyscache.c
*** src/backend/utils/cache/lsyscache.c 27 Jun 2003 00:33:25 -0000 1.100
--- src/backend/utils/cache/lsyscache.c 29 Jun 2003 16:26:36 -0000
***************
*** 719,724 ****
--- 719,758 ----
}
/*
+ * get_func_argtypes
+ * Given procedure id, return the function's argument types.
+ * Also pass back the number of arguments.
+ */
+ Oid *
+ get_func_argtypes(Oid funcid, int *nargs)
+ {
+ HeapTuple tp;
+ Form_pg_proc procstruct;
+ Oid *result = NULL;
+ int i;
+
+ tp = SearchSysCache(PROCOID,
+ ObjectIdGetDatum(funcid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "Function OID %u does not exist", funcid);
+
+ procstruct = (Form_pg_proc) GETSTRUCT(tp);
+ *nargs = (int) procstruct->pronargs;
+
+ if (*nargs > 0)
+ {
+ result = (Oid *) palloc(*nargs * sizeof(Oid));
+
+ for (i = 0; i < *nargs; i++)
+ result[i] = procstruct->proargtypes[i];
+ }
+
+ ReleaseSysCache(tp);
+ return result;
+ }
+
+ /*
* get_func_retset
* Given procedure id, return the function's proretset flag.
*/
Index: src/include/nodes/primnodes.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/nodes/primnodes.h,v
retrieving revision 1.86
diff -c -r1.86 primnodes.h
*** src/include/nodes/primnodes.h 29 Jun 2003 00:33:44 -0000 1.86
--- src/include/nodes/primnodes.h 29 Jun 2003 16:26:36 -0000
***************
*** 226,231 ****
--- 226,232 ----
Index agglevelsup; /* > 0 if agg belongs to outer query */
bool aggstar; /* TRUE if argument was really '*' */
bool aggdistinct; /* TRUE if it's agg(DISTINCT ...) */
+ List *args; /* arguments to the aggregate */
} Aggref;
/* ----------------
Index: src/include/optimizer/clauses.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/optimizer/clauses.h,v
retrieving revision 1.65
diff -c -r1.65 clauses.h
*** src/include/optimizer/clauses.h 25 Jun 2003 21:30:33 -0000 1.65
--- src/include/optimizer/clauses.h 29 Jun 2003 16:26:36 -0000
***************
*** 28,33 ****
--- 28,36 ----
extern Node *get_leftop(Expr *clause);
extern Node *get_rightop(Expr *clause);
+ extern Expr *make_funcclause(Oid funcid, Oid funcresulttype, bool funcretset,
+ CoercionForm
funcformat, List *funcargs);
+
extern bool not_clause(Node *clause);
extern Expr *make_notclause(Expr *notclause);
extern Expr *get_notclausearg(Expr *notclause);
Index: src/include/parser/parse_agg.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/parser/parse_agg.h,v
retrieving revision 1.26
diff -c -r1.26 parse_agg.h
*** src/include/parser/parse_agg.h 6 Jun 2003 15:04:03 -0000 1.26
--- src/include/parser/parse_agg.h 29 Jun 2003 16:26:36 -0000
***************
*** 18,22 ****
--- 18,30 ----
extern void transformAggregateCall(ParseState *pstate, Aggref *agg);
extern void parseCheckAggregates(ParseState *pstate, Query *qry);
+ extern void expand_aggregate(Oid agg_rt_basetype,
+ Oid agg_statetype,
+ Oid agg_fnoid,
+ Oid transfn_oid,
+ Oid finalfn_oid,
+ FuncExpr **transfnexpr,
+ FuncExpr **finalfnexpr,
+ Oid *transfn_arg1_type);
#endif /* PARSE_AGG_H */
Index: src/include/parser/parse_type.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/parser/parse_type.h,v
retrieving revision 1.24
diff -c -r1.24 parse_type.h
*** src/include/parser/parse_type.h 31 Aug 2002 22:10:47 -0000 1.24
--- src/include/parser/parse_type.h 29 Jun 2003 16:26:36 -0000
***************
*** 40,45 ****
--- 40,46 ----
extern Oid typeidTypeRelid(Oid type_id);
extern void parseTypeString(const char *str, Oid *type_id, int32 *typmod);
+ extern Oid resolve_type(Oid type_to_resolve, Oid context_type);
#define ISCOMPLEX(typeid) (typeidTypeRelid(typeid) != InvalidOid)
Index: src/include/utils/lsyscache.h
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/include/utils/lsyscache.h,v
retrieving revision 1.75
diff -c -r1.75 lsyscache.h
*** src/include/utils/lsyscache.h 27 Jun 2003 00:33:26 -0000 1.75
--- src/include/utils/lsyscache.h 29 Jun 2003 16:26:36 -0000
***************
*** 50,55 ****
--- 50,56 ----
extern RegProcedure get_oprjoin(Oid opno);
extern char *get_func_name(Oid funcid);
extern Oid get_func_rettype(Oid funcid);
+ extern Oid *get_func_argtypes(Oid funcid, int *nargs);
extern bool get_func_retset(Oid funcid);
extern bool func_strict(Oid funcid);
extern char func_volatile(Oid funcid);
-- Legend: ----------- -- A = type is ANY -- P = type is polymorphic -- N = type is non-polymorphic -- B = aggregate base type -- S = aggregate state type -- R = aggregate return type -- 1 = arg1 of a function -- 2 = arg2 of a function -- ag = aggregate -- tf = trans (state) function -- ff = final function -- rt = return type of a function -- -> = implies -- => = allowed -- !> = not allowed -- E = exists -- NE = not-exists -- -- Possible states: -- ---------------- -- B = (A || P || N) -- when (B = A) -> (tf2 = NE) -- S = (P || N) -- ff = (E || NE) -- tf1 = (P || N) -- tf2 = (NE || P || N) -- R = (P || N)
-- create functions for use as tf and ff with the needed combinations of argument
-- polymorphism, but within the constraints of valid aggregate functions, i.e. tf
-- arg1 and tf return type must match
-- polymorphic single arg transfn
CREATE OR REPLACE FUNCTION stfp(anyarray) returns anyarray as 'select $1' language
'sql';
-- non-polymorphic single arg transfn
CREATE OR REPLACE FUNCTION stfnp(int[]) returns int[] as 'select $1' language 'sql';
-- dual polymorphic transfn
CREATE OR REPLACE FUNCTION tfp(anyarray,anyelement) returns anyarray as 'select $1 ||
$2' language 'sql';
-- dual non-polymorphic transfn
CREATE OR REPLACE FUNCTION tfnp(int[],int) returns int[] as 'select $1 || $2' language
'sql';
-- arg1 only polymorphic transfn
CREATE OR REPLACE FUNCTION tf1p(anyarray,int) returns anyarray as 'select $1' language
'sql';
-- arg2 only polymorphic transfn
CREATE OR REPLACE FUNCTION tf2p(int[],anyelement) returns int[] as 'select $1'
language 'sql';
-- finalfn polymorphic
CREATE OR REPLACE FUNCTION ffp(anyarray) returns anyarray as 'select $1' language
'sql';
-- finalfn non-polymorphic
CREATE OR REPLACE FUNCTION ffnp(int[]) returns int[] as 'select $1' language 'sql';
-- Try to cover all the possible states:
--
-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn is
stfnp, tfnp,
-- or tf2p, we must use ffp as finalfn, because stfnp, tfnp, and tf2p do not return P.
Conversely,
-- in Cases 3 & 4, we are trying to return N. Therefore, if the transfn is stfp, tfp,
or tf1p,
-- we must use ffnp as finalfn, because stfp, tfp, and tf1p do not return N.
--
-- Case1 (R = P) && (B = A)
-- ------------------------
-- S tf1
-- -------
-- N N
-- should CREATE
CREATE AGGREGATE myaggp01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], FINALFUNC
= ffp, INITCOND = '{}');
-- P N
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
CREATE AGGREGATE myaggp02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
-- N P
-- should CREATE
CREATE AGGREGATE myaggp03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp03b(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], INITCOND =
'{}');
-- P P
-- should ERROR: we have no way to resolve S
CREATE AGGREGATE myaggp04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, FINALFUNC
= ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp04b(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, INITCOND
= '{}');
-- Case2 (R = P) && ((B = P) || (B = N))
-- -------------------------------------
-- S tf1 B tf2
-- -----------------------
-- N N N N
-- should CREATE
CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
-- N N N P
-- should CREATE
CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
-- N N P N
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
-- N N P P
-- should CREATE
CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
-- N P N N
-- should CREATE
CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], INITCOND =
'{}');
-- N P N P
-- should CREATE
CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], INITCOND =
'{}');
-- N P P N
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
INITCOND = '{}');
-- N P P P
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], INITCOND
= '{}');
-- P N N N
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
-- P N N P
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
-- P N P N
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
-- P N P P
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
-- P P N N
-- should ERROR: we have no way to resolve S
CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, INITCOND =
'{}');
-- P P N P
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, INITCOND =
'{}');
-- P P P N
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
INITCOND = '{}');
-- P P P P
-- should CREATE
CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
INITCOND = '{}');
-- Case3 (R = N) && (B = A)
-- ------------------------
-- S tf1
-- -------
-- N N
-- should CREATE
CREATE AGGREGATE myaggn01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], FINALFUNC
= ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn01b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], INITCOND =
'{}');
-- P N
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
CREATE AGGREGATE myaggn02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn02b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray, INITCOND
= '{}');
-- N P
-- should CREATE
CREATE AGGREGATE myaggn03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], FINALFUNC =
ffnp, INITCOND = '{}');
-- P P
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, FINALFUNC
= ffnp, INITCOND = '{}');
-- Case4 (R = N) && ((B = P) || (B = N))
-- -------------------------------------
-- S tf1 B tf2
-- -----------------------
-- N N N N
-- should CREATE
CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], INITCOND =
'{}');
-- N N N P
-- should CREATE
CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], INITCOND =
'{}');
-- N N P N
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
INITCOND = '{}');
-- N N P P
-- should CREATE
CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
INITCOND = '{}');
-- N P N N
-- should CREATE
CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
-- N P N P
-- should CREATE
CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
-- N P P N
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
-- N P P P
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
-- P N N N
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, INITCOND =
'{}');
-- P N N P
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, INITCOND =
'{}');
-- P N P N
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
INITCOND = '{}');
-- P N P P
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
INITCOND = '{}');
-- P P N N
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
-- P P N P
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
-- P P P N
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
-- P P P P
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
-- create test data for polymorphic aggregates
create table t(f1 int, f2 int[], f3 text);
insert into t values(1,array[1],'a');
insert into t values(1,array[11],'b');
insert into t values(1,array[111],'c');
insert into t values(2,array[2],'a');
insert into t values(2,array[22],'b');
insert into t values(2,array[222],'c');
insert into t values(3,array[3],'a');
insert into t values(3,array[3],'b');
-- test the successfully created polymorphic aggregates
select f3, myaggp01a(*) from t group by f3;
select f3, myaggp03a(*) from t group by f3;
select f3, myaggp03b(*) from t group by f3;
select f3, myaggp05a(f1) from t group by f3;
select f3, myaggp06a(f1) from t group by f3;
select f3, myaggp08a(f1) from t group by f3;
select f3, myaggp09a(f1) from t group by f3;
select f3, myaggp09b(f1) from t group by f3;
select f3, myaggp10a(f1) from t group by f3;
select f3, myaggp10b(f1) from t group by f3;
select f3, myaggp20a(f1) from t group by f3;
select f3, myaggp20b(f1) from t group by f3;
select f3, myaggn01a(*) from t group by f3;
select f3, myaggn01b(*) from t group by f3;
select f3, myaggn03a(*) from t group by f3;
select f3, myaggn05a(f1) from t group by f3;
select f3, myaggn05b(f1) from t group by f3;
select f3, myaggn06a(f1) from t group by f3;
select f3, myaggn06b(f1) from t group by f3;
select f3, myaggn08a(f1) from t group by f3;
select f3, myaggn08b(f1) from t group by f3;
select f3, myaggn09a(f1) from t group by f3;
select f3, myaggn10a(f1) from t group by f3;
-- Legend:
-----------
-- A = type is ANY
-- P = type is polymorphic
-- N = type is non-polymorphic
-- B = aggregate base type
-- S = aggregate state type
-- R = aggregate return type
-- 1 = arg1 of a function
-- 2 = arg2 of a function
-- ag = aggregate
-- tf = trans (state) function
-- ff = final function
-- rt = return type of a function
-- -> = implies
-- => = allowed
-- !> = not allowed
-- E = exists
-- NE = not-exists
--
-- Possible states:
-- ----------------
-- B = (A || P || N)
-- when (B = A) -> (tf2 = NE)
-- S = (P || N)
-- ff = (E || NE)
-- tf1 = (P || N)
-- tf2 = (NE || P || N)
-- R = (P || N)
-- create functions for use as tf and ff with the needed combinations of argument
-- polymorphism, but within the constraints of valid aggregate functions, i.e. tf
-- arg1 and tf return type must match
-- polymorphic single arg transfn
CREATE OR REPLACE FUNCTION stfp(anyarray) returns anyarray as 'select $1' language
'sql';
CREATE FUNCTION
-- non-polymorphic single arg transfn
CREATE OR REPLACE FUNCTION stfnp(int[]) returns int[] as 'select $1' language 'sql';
CREATE FUNCTION
-- dual polymorphic transfn
CREATE OR REPLACE FUNCTION tfp(anyarray,anyelement) returns anyarray as 'select $1 ||
$2' language 'sql';
CREATE FUNCTION
-- dual non-polymorphic transfn
CREATE OR REPLACE FUNCTION tfnp(int[],int) returns int[] as 'select $1 || $2' language
'sql';
CREATE FUNCTION
-- arg1 only polymorphic transfn
CREATE OR REPLACE FUNCTION tf1p(anyarray,int) returns anyarray as 'select $1' language
'sql';
CREATE FUNCTION
-- arg2 only polymorphic transfn
CREATE OR REPLACE FUNCTION tf2p(int[],anyelement) returns int[] as 'select $1'
language 'sql';
CREATE FUNCTION
-- finalfn polymorphic
CREATE OR REPLACE FUNCTION ffp(anyarray) returns anyarray as 'select $1' language
'sql';
CREATE FUNCTION
-- finalfn non-polymorphic
CREATE OR REPLACE FUNCTION ffnp(int[]) returns int[] as 'select $1' language 'sql';
CREATE FUNCTION
-- Try to cover all the possible states:
--
-- Note: in Cases 1 & 2, we are trying to return P. Therefore, if the transfn is
stfnp, tfnp,
-- or tf2p, we must use ffp as finalfn, because stfnp, tfnp, and tf2p do not return P.
Conversely,
-- in Cases 3 & 4, we are trying to return N. Therefore, if the transfn is stfp, tfp,
or tf1p,
-- we must use ffnp as finalfn, because stfp, tfp, and tf1p do not return N.
--
-- Case1 (R = P) && (B = A)
-- ------------------------
-- S tf1
-- -------
-- N N
-- should CREATE
CREATE AGGREGATE myaggp01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], FINALFUNC
= ffp, INITCOND = '{}');
CREATE AGGREGATE
-- P N
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
CREATE AGGREGATE myaggp02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function stfnp(anyarray) does not exist
-- N P
-- should CREATE
CREATE AGGREGATE myaggp03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggp03b(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], INITCOND =
'{}');
CREATE AGGREGATE
-- P P
-- should ERROR: we have no way to resolve S
CREATE AGGREGATE myaggp04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, FINALFUNC
= ffp, INITCOND = '{}');
ERROR: an aggregate returning ANYARRAY or ANYELEMENT must also have either of the
them as its base type
CREATE AGGREGATE myaggp04b(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, INITCOND
= '{}');
ERROR: an aggregate returning ANYARRAY or ANYELEMENT must also have either of the
them as its base type
-- Case2 (R = P) && ((B = P) || (B = N))
-- -------------------------------------
-- S tf1 B tf2
-- -----------------------
-- N N N N
-- should CREATE
CREATE AGGREGATE myaggp05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE
-- N N N P
-- should CREATE
CREATE AGGREGATE myaggp06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE
-- N N P N
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
CREATE AGGREGATE myaggp07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(integer[], anyelement) does not exist
-- N N P P
-- should CREATE
CREATE AGGREGATE myaggp08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE
-- N P N N
-- should CREATE
CREATE AGGREGATE myaggp09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggp09b(BASETYPE = int, SFUNC = tf1p, STYPE = int[], INITCOND =
'{}');
CREATE AGGREGATE
-- N P N P
-- should CREATE
CREATE AGGREGATE myaggp10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], FINALFUNC =
ffp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggp10b(BASETYPE = int, SFUNC = tfp, STYPE = int[], INITCOND =
'{}');
CREATE AGGREGATE
-- N P P N
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
CREATE AGGREGATE myaggp11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(integer[], anyelement) does not exist
CREATE AGGREGATE myaggp11b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(integer[], anyelement) does not exist
-- N P P P
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
CREATE AGGREGATE myaggp12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tfp(integer[], anyelement) does not exist
CREATE AGGREGATE myaggp12b(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[], INITCOND
= '{}');
ERROR: AggregateCreate: function tfp(integer[], anyelement) does not exist
-- P N N N
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggp13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(anyarray, integer) does not exist
-- P N N P
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggp14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tf2p(anyarray, integer) does not exist
-- P N P N
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggp15a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(anyarray, anyelement) does not exist
-- P N P P
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggp16a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tf2p(anyarray, anyelement) does not exist
-- P P N N
-- should ERROR: we have no way to resolve S
CREATE AGGREGATE myaggp17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
ERROR: an aggregate returning ANYARRAY or ANYELEMENT must also have either of the
them as its base type
CREATE AGGREGATE myaggp17b(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, INITCOND =
'{}');
ERROR: an aggregate returning ANYARRAY or ANYELEMENT must also have either of the
them as its base type
-- P P N P
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
CREATE AGGREGATE myaggp18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, FINALFUNC =
ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tfp(anyarray, integer) does not exist
CREATE AGGREGATE myaggp18b(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, INITCOND =
'{}');
ERROR: AggregateCreate: function tfp(anyarray, integer) does not exist
-- P P P N
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
CREATE AGGREGATE myaggp19a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(anyarray, anyelement) does not exist
CREATE AGGREGATE myaggp19b(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(anyarray, anyelement) does not exist
-- P P P P
-- should CREATE
CREATE AGGREGATE myaggp20a(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
FINALFUNC = ffp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggp20b(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
INITCOND = '{}');
CREATE AGGREGATE
-- Case3 (R = N) && (B = A)
-- ------------------------
-- S tf1
-- -------
-- N N
-- should CREATE
CREATE AGGREGATE myaggn01a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], FINALFUNC
= ffnp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggn01b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = int4[], INITCOND =
'{}');
CREATE AGGREGATE
-- P N
-- should ERROR: stfnp(anyarray) not matched by stfnp(int[])
CREATE AGGREGATE myaggn02a(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function stfnp(anyarray) does not exist
CREATE AGGREGATE myaggn02b(BASETYPE = "ANY", SFUNC = stfnp, STYPE = anyarray, INITCOND
= '{}');
ERROR: AggregateCreate: function stfnp(anyarray) does not exist
-- N P
-- should CREATE
CREATE AGGREGATE myaggn03a(BASETYPE = "ANY", SFUNC = stfp, STYPE = int4[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE
-- P P
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn04a(BASETYPE = "ANY", SFUNC = stfp, STYPE = anyarray, FINALFUNC
= ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function ffnp(anyarray) does not exist
-- Case4 (R = N) && ((B = P) || (B = N))
-- -------------------------------------
-- S tf1 B tf2
-- -----------------------
-- N N N N
-- should CREATE
CREATE AGGREGATE myaggn05a(BASETYPE = int, SFUNC = tfnp, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggn05b(BASETYPE = int, SFUNC = tfnp, STYPE = int[], INITCOND =
'{}');
CREATE AGGREGATE
-- N N N P
-- should CREATE
CREATE AGGREGATE myaggn06a(BASETYPE = int, SFUNC = tf2p, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggn06b(BASETYPE = int, SFUNC = tf2p, STYPE = int[], INITCOND =
'{}');
CREATE AGGREGATE
-- N N P N
-- should ERROR: tfnp(int[], anyelement) not matched by tfnp(int[], int)
CREATE AGGREGATE myaggn07a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(integer[], anyelement) does not exist
CREATE AGGREGATE myaggn07b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = int[],
INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(integer[], anyelement) does not exist
-- N N P P
-- should CREATE
CREATE AGGREGATE myaggn08a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
CREATE AGGREGATE
CREATE AGGREGATE myaggn08b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = int[],
INITCOND = '{}');
CREATE AGGREGATE
-- N P N N
-- should CREATE
CREATE AGGREGATE myaggn09a(BASETYPE = int, SFUNC = tf1p, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE
-- N P N P
-- should CREATE
CREATE AGGREGATE myaggn10a(BASETYPE = int, SFUNC = tfp, STYPE = int[], FINALFUNC =
ffnp, INITCOND = '{}');
CREATE AGGREGATE
-- N P P N
-- should ERROR: tf1p(int[],anyelement) not matched by tf1p(anyarray,int)
CREATE AGGREGATE myaggn11a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(integer[], anyelement) does not exist
-- N P P P
-- should ERROR: tfp(int[],anyelement) not matched by tfp(anyarray,anyelement)
CREATE AGGREGATE myaggn12a(BASETYPE = anyelement, SFUNC = tfp, STYPE = int[],
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tfp(integer[], anyelement) does not exist
-- P N N N
-- should ERROR: tfnp(anyarray, int) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggn13a(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(anyarray, integer) does not exist
CREATE AGGREGATE myaggn13b(BASETYPE = int, SFUNC = tfnp, STYPE = anyarray, INITCOND =
'{}');
ERROR: AggregateCreate: function tfnp(anyarray, integer) does not exist
-- P N N P
-- should ERROR: tf2p(anyarray, int) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggn14a(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tf2p(anyarray, integer) does not exist
CREATE AGGREGATE myaggn14b(BASETYPE = int, SFUNC = tf2p, STYPE = anyarray, INITCOND =
'{}');
ERROR: AggregateCreate: function tf2p(anyarray, integer) does not exist
-- P N P N
-- should ERROR: tfnp(anyarray, anyelement) not matched by tfnp(int[],int)
CREATE AGGREGATE myaggn15a(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(anyarray, anyelement) does not exist
CREATE AGGREGATE myaggn15b(BASETYPE = anyelement, SFUNC = tfnp, STYPE = anyarray,
INITCOND = '{}');
ERROR: AggregateCreate: function tfnp(anyarray, anyelement) does not exist
-- P N P P
-- should ERROR: tf2p(anyarray, anyelement) not matched by tf2p(int[],anyelement)
CREATE AGGREGATE myaggn16a(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tf2p(anyarray, anyelement) does not exist
CREATE AGGREGATE myaggn16b(BASETYPE = anyelement, SFUNC = tf2p, STYPE = anyarray,
INITCOND = '{}');
ERROR: AggregateCreate: function tf2p(anyarray, anyelement) does not exist
-- P P N N
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn17a(BASETYPE = int, SFUNC = tf1p, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function ffnp(anyarray) does not exist
-- P P N P
-- should ERROR: tfp(anyarray, int) not matched by tfp(anyarray, anyelement)
CREATE AGGREGATE myaggn18a(BASETYPE = int, SFUNC = tfp, STYPE = anyarray, FINALFUNC =
ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tfp(anyarray, integer) does not exist
-- P P P N
-- should ERROR: tf1p(anyarray, anyelement) not matched by tf1p(anyarray, int)
CREATE AGGREGATE myaggn19a(BASETYPE = anyelement, SFUNC = tf1p, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function tf1p(anyarray, anyelement) does not exist
-- P P P P
-- should ERROR: ffnp(anyarray) not matched by ffnp(int[])
CREATE AGGREGATE myaggn20a(BASETYPE = anyelement, SFUNC = tfp, STYPE = anyarray,
FINALFUNC = ffnp, INITCOND = '{}');
ERROR: AggregateCreate: function ffnp(anyarray) does not exist
-- create test data for polymorphic aggregates
create table t(f1 int, f2 int[], f3 text);
CREATE TABLE
insert into t values(1,array[1],'a');
INSERT 1253618 1
insert into t values(1,array[11],'b');
INSERT 1253619 1
insert into t values(1,array[111],'c');
INSERT 1253620 1
insert into t values(2,array[2],'a');
INSERT 1253621 1
insert into t values(2,array[22],'b');
INSERT 1253622 1
insert into t values(2,array[222],'c');
INSERT 1253623 1
insert into t values(3,array[3],'a');
INSERT 1253624 1
insert into t values(3,array[3],'b');
INSERT 1253625 1
-- test the successfully created polymorphic aggregates
select f3, myaggp01a(*) from t group by f3;
f3 | myaggp01a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp03a(*) from t group by f3;
f3 | myaggp03a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp03b(*) from t group by f3;
f3 | myaggp03b
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp05a(f1) from t group by f3;
f3 | myaggp05a
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggp06a(f1) from t group by f3;
f3 | myaggp06a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp08a(f1) from t group by f3;
f3 | myaggp08a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp09a(f1) from t group by f3;
f3 | myaggp09a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp09b(f1) from t group by f3;
f3 | myaggp09b
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggp10a(f1) from t group by f3;
f3 | myaggp10a
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggp10b(f1) from t group by f3;
f3 | myaggp10b
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggp20a(f1) from t group by f3;
f3 | myaggp20a
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggp20b(f1) from t group by f3;
f3 | myaggp20b
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggn01a(*) from t group by f3;
f3 | myaggn01a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn01b(*) from t group by f3;
f3 | myaggn01b
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn03a(*) from t group by f3;
f3 | myaggn03a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn05a(f1) from t group by f3;
f3 | myaggn05a
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggn05b(f1) from t group by f3;
f3 | myaggn05b
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
select f3, myaggn06a(f1) from t group by f3;
f3 | myaggn06a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn06b(f1) from t group by f3;
f3 | myaggn06b
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn08a(f1) from t group by f3;
f3 | myaggn08a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn08b(f1) from t group by f3;
f3 | myaggn08b
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn09a(f1) from t group by f3;
f3 | myaggn09a
----+-----------
b | {}
a | {}
c | {}
(3 rows)
select f3, myaggn10a(f1) from t group by f3;
f3 | myaggn10a
----+-----------
b | {1,2,3}
a | {1,2,3}
c | {1,2}
(3 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
