Tom Lane wrote:
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

Reply via email to