> > you can check it? We can test, how performance lost we get. As second > benefit we can get numbers for introduction new optimized array builder >
array_agg(anyarray) with deconstruct_array, unchanged accumArrayResult and makeMdArrayResult: > INSERT 0 1 > Time: 852,527 ms > INSERT 0 1 > Time: 844,275 ms > INSERT 0 1 > Time: 858,855 ms > INSERT 0 1 > Time: 861,072 ms > INSERT 0 1 > Time: 952,006 ms > INSERT 0 1 > Time: 953,918 ms > INSERT 0 1 > Time: 926,945 ms > INSERT 0 1 > Time: 923,692 ms > INSERT 0 1 > Time: 940,916 ms > INSERT 0 1 > Time: 948,700 ms > INSERT 0 1 > Time: 933,333 ms > INSERT 0 1 > Time: 948,869 ms > INSERT 0 1 > Time: 847,113 ms > INSERT 0 1 > Time: 908,572 ms Total: 12776.83 Avg: 912,63 with last patch (v10): > INSERT 0 1 > Time: 643,339 ms > INSERT 0 1 > Time: 608,010 ms > INSERT 0 1 > Time: 610,465 ms > INSERT 0 1 > Time: 613,931 ms > INSERT 0 1 > Time: 616,466 ms > INSERT 0 1 > Time: 634,754 ms > INSERT 0 1 > Time: 683,566 ms > INSERT 0 1 > Time: 656,665 ms > INSERT 0 1 > Time: 630,096 ms > INSERT 0 1 > Time: 607,564 ms > INSERT 0 1 > Time: 610,353 ms > INSERT 0 1 > Time: 626,816 ms > INSERT 0 1 > Time: 610,450 ms > INSERT 0 1 > Time: 614,342 ms > Total: 8842,7 > Avg: 631,6 It's 30% faster (i tried varlena element - text). I tried several times and it's consistent in +/- 30%. quick & dirty non-optimized patch and the test script attached. Regards, -- Ali Akbar
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7e5bcd9..f59738a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12046,6 +12046,22 @@ NULL baz</literallayout>(3 rows)</entry> <row> <entry> <indexterm> + <primary>array_agg</primary> + </indexterm> + <function>array_agg(<replaceable class="parameter">anyarray</replaceable>)</function> + </entry> + <entry> + any + </entry> + <entry> + the same array type as input type + </entry> + <entry>input arrays, aggregated into higher-order multidimesional array. Rejects NULL and empty array as input.</entry> + </row> + + <row> + <entry> + <indexterm> <primary>average</primary> </indexterm> <indexterm> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 2f0680f..8c182a4 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -2238,6 +2238,11 @@ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ----------------------------------------------------------------------- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413} + +SELECT ARRAY(SELECT array(select i) FROM generate_series(1,5) a(i)); + array +----------------------- + {{1},{2},{3},{4},{5}} (1 row) </programlisting> The subquery must return a single column. The resulting diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 41e973b..0261fcb 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -108,12 +108,16 @@ exprType(const Node *expr) type = exprType((Node *) tent->expr); if (sublink->subLinkType == ARRAY_SUBLINK) { - type = get_array_type(type); - if (!OidIsValid(type)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("could not find array type for data type %s", - format_type_be(exprType((Node *) tent->expr))))); + if (!OidIsValid(get_element_type(type))) + { + /* not array, so check for its array type */ + type = get_array_type(type); + if (!OidIsValid(type)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find array type for data type %s", + format_type_be(exprType((Node *) tent->expr))))); + } } } else if (sublink->subLinkType == MULTIEXPR_SUBLINK) @@ -139,12 +143,16 @@ exprType(const Node *expr) type = subplan->firstColType; if (subplan->subLinkType == ARRAY_SUBLINK) { - type = get_array_type(type); - if (!OidIsValid(type)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("could not find array type for data type %s", - format_type_be(subplan->firstColType)))); + if (!OidIsValid(get_element_type(type))) + { + /* not array, so check for its array type */ + type = get_array_type(type); + if (!OidIsValid(type)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find array type for data type %s", + format_type_be(subplan->firstColType)))); + } } } else if (subplan->subLinkType == MULTIEXPR_SUBLINK) diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 3e7dc85..8fc8b49 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -668,10 +668,16 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot, Assert(!te->resjunk); Assert(testexpr == NULL); - arraytype = get_array_type(exprType((Node *) te->expr)); - if (!OidIsValid(arraytype)) - elog(ERROR, "could not find array type for datatype %s", - format_type_be(exprType((Node *) te->expr))); + + arraytype = exprType((Node *) te->expr); + if (!OidIsValid(get_element_type(arraytype))) + { + /* not array, so get the array type */ + arraytype = get_array_type(exprType((Node *) te->expr)); + if (!OidIsValid(arraytype)) + elog(ERROR, "could not find array type for datatype %s", + format_type_be(exprType((Node *) te->expr))); + } prm = generate_new_param(root, arraytype, exprTypmod((Node *) te->expr), diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c index 831466d..35f427d 100644 --- a/src/backend/utils/adt/array_userfuncs.c +++ b/src/backend/utils/adt/array_userfuncs.c @@ -16,6 +16,8 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" +static Datum array_agg_transfn_internal(PG_FUNCTION_ARGS, + const char* fname); /*----------------------------------------------------------------------------- * array_push : @@ -476,6 +478,19 @@ create_singleton_array(FunctionCallInfo fcinfo, Datum array_agg_transfn(PG_FUNCTION_ARGS) { + return array_agg_transfn_internal(fcinfo, "array_agg_transfn"); +} + +/* + * internal function of ARRAY_AGG + * called by array_agg_transfn and array_agg_transfn_array + * + * fname - function name (for error reporting) + */ +static Datum +array_agg_transfn_internal(PG_FUNCTION_ARGS, + const char* fname) +{ Oid arg1_typeid = get_fn_expr_argtype(fcinfo->flinfo, 1); MemoryContext aggcontext; ArrayBuildState *state; @@ -489,7 +504,7 @@ array_agg_transfn(PG_FUNCTION_ARGS) if (!AggCheckCallContext(fcinfo, &aggcontext)) { /* cannot be called directly because of internal-type argument */ - elog(ERROR, "array_agg_transfn called in non-aggregate context"); + elog(ERROR, "%s called in non-aggregate context", fname); } state = PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0); @@ -513,8 +528,8 @@ array_agg_finalfn(PG_FUNCTION_ARGS) { Datum result; ArrayBuildState *state; - int dims[1]; - int lbs[1]; + int dims[1]; + int lbs[1]; /* * Test for null before Asserting we are in right context. This is to @@ -529,8 +544,131 @@ array_agg_finalfn(PG_FUNCTION_ARGS) state = (ArrayBuildState *) PG_GETARG_POINTER(0); + /* + * Make the result. We cannot release the ArrayBuildState because + * sometimes aggregate final functions are re-executed. Rather, it is + * nodeAgg.c's responsibility to reset the aggcontext when it's safe to do + * so. + */ dims[0] = state->nelems; lbs[0] = 1; + result = makeMdArrayResult(state, + 1, dims, lbs, + CurrentMemoryContext, false + ); + + PG_RETURN_DATUM(result); +} + +typedef struct ArrayBuildStateA { + ArrayBuildState * astate; + int ndims; + int *dims; + int *lbs; +} ArrayBuildStateA; + +/* + * ARRAY_AGG(anyarray) aggregate function + */ +Datum +array_agg_anyarray_transfn(PG_FUNCTION_ARGS) +{ + Oid arg1_typeid = get_fn_expr_argtype(fcinfo->flinfo, 1); + MemoryContext aggcontext,oldcontext; + ArrayBuildStateA *state; + ArrayType *elem; + + int16 typlen; + bool typbyval; + char typalign; + + Datum *elements; + bool *nulls; + int nelem; + + int i,j; + + if (arg1_typeid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine input data type"))); + + if (!AggCheckCallContext(fcinfo, &aggcontext)) + { + /* cannot be called directly because of internal-type argument */ + elog(ERROR, "array_agg_anyarray_transfn called in non-aggregate context"); + } + + state = PG_ARGISNULL(0) ? NULL : (ArrayBuildStateA *) PG_GETARG_POINTER(0); + + elem = PG_ARGISNULL(1) ? NULL : PG_GETARG_ARRAYTYPE_P(1); + + oldcontext = MemoryContextSwitchTo(aggcontext); + if (state == NULL) + { + state = (ArrayBuildStateA *) palloc(sizeof(ArrayBuildStateA)); + state->astate = NULL; + state->ndims = ARR_NDIM(elem) + 1; + state->dims = (int *) palloc(state->ndims * sizeof(int)); + state->lbs = (int *) palloc(state->ndims * sizeof(int)); + + memcpy(&(state->dims[1]), ARR_DIMS(elem), (state->ndims - 1) * sizeof(int)); + memcpy(&(state->lbs[1]), ARR_LBOUND(elem), (state->ndims - 1) * sizeof(int)); + + state->lbs[0] = 1; + state->dims[0] = 1; + } + else + { + //TODO: check for correct dimensions + state->dims[0] += 1; + } + + arg1_typeid = ARR_ELEMTYPE(elem); + get_typlenbyvalalign(arg1_typeid, + &typlen, + &typbyval, + &typalign); + + deconstruct_array(elem, arg1_typeid, typlen, typbyval, typalign, + &elements, &nulls, &nelem); + + for (i = 0; i < nelem; i++) + { + state->astate = accumArrayResult(state->astate, + elements[i], + nulls[i], + arg1_typeid, + aggcontext); + } + MemoryContextSwitchTo(oldcontext); + /* + * The transition type for array_agg() is declared to be "internal", which + * is a pass-by-value type the same size as a pointer. So we can safely + * pass the ArrayBuildState pointer through nodeAgg.c's machinations. + */ + PG_RETURN_POINTER(state); +} + +Datum +array_agg_anyarray_finalfn(PG_FUNCTION_ARGS) +{ + Datum result; + ArrayBuildStateA *state; + int i; + + /* + * Test for null before Asserting we are in right context. This is to + * avoid possible Assert failure in 8.4beta installations, where it is + * possible for users to create NULL constants of type internal. + */ + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); /* returns null iff no input values */ + + /* cannot be called directly because of internal-type argument */ + Assert(AggCheckCallContext(fcinfo, NULL)); + + state = (ArrayBuildStateA *) PG_GETARG_POINTER(0); /* * Make the result. We cannot release the ArrayBuildState because @@ -538,9 +676,11 @@ array_agg_finalfn(PG_FUNCTION_ARGS) * nodeAgg.c's responsibility to reset the aggcontext when it's safe to do * so. */ - result = makeMdArrayResult(state, 1, dims, lbs, - CurrentMemoryContext, - false); + + result = makeMdArrayResult(state->astate, + state->ndims, state->dims, state->lbs, + CurrentMemoryContext, false + ); PG_RETURN_DATUM(result); } diff --git a/src/backend/utils/adt/arrayutils.c b/src/backend/utils/adt/arrayutils.c index 477ccad..af5622a 100644 --- a/src/backend/utils/adt/arrayutils.c +++ b/src/backend/utils/adt/arrayutils.c @@ -106,8 +106,8 @@ ArrayGetNItems(int ndim, const int *dims) if ((Size) ret > MaxArraySize) ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), - errmsg("array size exceeds the maximum allowed (%d)", - (int) MaxArraySize))); + errmsg("array size (%d) exceeds the maximum allowed (%d)", + (int) ret, (int) MaxArraySize))); return (int) ret; } diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 3ba9e5e..2005199 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -275,6 +275,7 @@ DATA(insert ( 2901 n 0 xmlconcat2 - - - - f f 0 142 0 0 0 _null_ /* array */ DATA(insert ( 2335 n 0 array_agg_transfn array_agg_finalfn - - - t f 0 2281 0 0 0 _null_ _null_ )); +DATA(insert ( 6005 n 0 array_agg_anyarray_transfn array_agg_anyarray_finalfn - - - t f 0 2281 0 0 0 _null_ _null_ )); /* text */ DATA(insert ( 3538 n 0 string_agg_transfn string_agg_finalfn - - - f f 0 2281 0 0 0 _null_ _null_ )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b6dc1b8..9273c1f 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -879,11 +879,17 @@ DATA(insert OID = 3167 ( array_remove PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 DESCR("remove any occurrences of an element from an array"); DATA(insert OID = 3168 ( array_replace PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 2277 "2277 2283 2283" _null_ _null_ _null_ _null_ array_replace _null_ _null_ _null_ )); DESCR("replace any occurrences of an element in an array"); -DATA(insert OID = 2333 ( array_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2283" _null_ _null_ _null_ _null_ array_agg_transfn _null_ _null_ _null_ )); +DATA(insert OID = 2333 ( array_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2776" _null_ _null_ _null_ _null_ array_agg_transfn _null_ _null_ _null_ )); DESCR("aggregate transition function"); -DATA(insert OID = 2334 ( array_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2281 2283" _null_ _null_ _null_ _null_ array_agg_finalfn _null_ _null_ _null_ )); +DATA(insert OID = 2334 ( array_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2281 2776" _null_ _null_ _null_ _null_ array_agg_finalfn _null_ _null_ _null_ )); DESCR("aggregate final function"); -DATA(insert OID = 2335 ( array_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 2277 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ )); +DATA(insert OID = 2335 ( array_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 2277 "2776" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ )); +DESCR("concatenate aggregate input into an array"); +DATA(insert OID = 6003 ( array_agg_anyarray_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2277" _null_ _null_ _null_ _null_ array_agg_anyarray_transfn _null_ _null_ _null_ )); +DESCR("aggregate transition function"); +DATA(insert OID = 6004 ( array_agg_anyarray_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2281 2277" _null_ _null_ _null_ _null_ array_agg_anyarray_finalfn _null_ _null_ _null_ )); +DESCR("aggregate final function"); +DATA(insert OID = 6005 ( array_agg PGNSP PGUID 12 1 0 0 0 t f f f f f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ )); DESCR("concatenate aggregate input into an array"); DATA(insert OID = 3218 ( width_bucket PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "2283 2277" _null_ _null_ _null_ _null_ width_bucket_array _null_ _null_ _null_ )); DESCR("bucket number of operand given a sorted array of bucket lower bounds"); diff --git a/src/include/utils/array.h b/src/include/utils/array.h index e744314..b4dd433 100644 --- a/src/include/utils/array.h +++ b/src/include/utils/array.h @@ -292,6 +292,8 @@ extern ArrayType *create_singleton_array(FunctionCallInfo fcinfo, extern Datum array_agg_transfn(PG_FUNCTION_ARGS); extern Datum array_agg_finalfn(PG_FUNCTION_ARGS); +extern Datum array_agg_anyarray_transfn(PG_FUNCTION_ARGS); +extern Datum array_agg_anyarray_finalfn(PG_FUNCTION_ARGS); /* * prototypes for functions defined in array_typanalyze.c diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 58df854..607aeea 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -914,6 +914,76 @@ select array_agg(distinct a order by a desc nulls last) {3,2,1,NULL} (1 row) +-- array_agg(anyarray) +select array_agg(ar) + from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar); + array_agg +--------------- + {{1,2},{3,4}} +(1 row) + +select array_agg(distinct ar order by ar desc) + from (select array[i / 2] from generate_series(1,10) a(i)) b(ar); + array_agg +--------------------------- + {{5},{4},{3},{2},{1},{0}} +(1 row) + +select array_agg(ar) + from (select array_agg(array[i, i+1, i-1]) + from generate_series(1,2) a(i)) b(ar); + array_agg +--------------------- + {{{1,2,0},{2,3,1}}} +(1 row) + +-- array_agg(anyarray), varlena types +select array_agg(array[1.2,1.3,1.4]) from generate_series(1,3); + array_agg +--------------------------------------------- + {{1.2,1.3,1.4},{1.2,1.3,1.4},{1.2,1.3,1.4}} +(1 row) + +select array_agg(array['Hello','Hohoho','Hi']) from generate_series(1,3); + array_agg +--------------------------------------------------------- + {{Hello,Hohoho,Hi},{Hello,Hohoho,Hi},{Hello,Hohoho,Hi}} +(1 row) + +-- array_agg(anyarray), arrays with nulls +select array_agg(array[i, null, i+1, null, i+2]) from generate_series(1,3) g(i); + array_agg +--------------------------------------------------------- + {{1,NULL,2,NULL,3},{2,NULL,3,NULL,4},{3,NULL,4,NULL,5}} +(1 row) + +select array_agg(array[1.1+ i, null, 1.1+i+1, null, 1.1+i+2]) from generate_series(1,3) g(i); + array_agg +--------------------------------------------------------------------------- + {{2.1,NULL,3.1,NULL,4.1},{3.1,NULL,4.1,NULL,5.1},{4.1,NULL,5.1,NULL,6.1}} +(1 row) + +select array_agg(array[null, 'Hello','Hohoho', null,'Hi']) from generate_series(1,3); + array_agg +--------------------------------------------------------------------------------------- + {{NULL,Hello,Hohoho,NULL,Hi},{NULL,Hello,Hohoho,NULL,Hi},{NULL,Hello,Hohoho,NULL,Hi}} +(1 row) + +select array_agg(array[[null, 'Hello', null, 'Hi'],['Hello', null, 'Hi', null]]) from generate_series(1,2); + array_agg +------------------------------------------------------------------------------------------- + {{{NULL,Hello,NULL,Hi},{Hello,NULL,Hi,NULL}},{{NULL,Hello,NULL,Hi},{Hello,NULL,Hi,NULL}}} +(1 row) + +-- errors +select array_agg('{}'::int[]) from generate_series(1,2); +ERROR: cannot accumulate empty arrays +select array_agg(null::int[]) from generate_series(1,2); +ERROR: cannot accumulate null arrays +select array_agg(ar) + from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar); +ERROR: cannot accumulate incompatible arrays +DETAIL: Arrays with differing element dimensions are not compatible for concatenation. -- multi-arg aggs, strict/nonstrict, distinct/order by select aggfstr(a,b,c) from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c); diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 46eff67..e80ebec 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -1521,6 +1521,23 @@ select array_agg(unique1) from tenk1 where unique1 < -15; (1 row) +select array(select unique1 from tenk1 where unique1 < 15 order by unique1); + array +-------------------------------------- + {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14} +(1 row) + +select array(select array[i,i/2] from generate_series(1,5) a(i)); + array +--------------------------------- + {{1,0},{2,1},{3,1},{4,2},{5,2}} +(1 row) + +-- cannot accumulate null arrays and empty arrays +select array(select null::int[]); +ERROR: cannot accumulate null arrays +select array(select '{}'::int[]); +ERROR: cannot accumulate empty arrays select unnest(array[1,2,3]); unnest -------- diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 8096a6f..a70419f 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -322,6 +322,32 @@ select array_agg(distinct a order by a desc) select array_agg(distinct a order by a desc nulls last) from (values (1),(2),(1),(3),(null),(2)) v(a); +-- array_agg(anyarray) +select array_agg(ar) + from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar); +select array_agg(distinct ar order by ar desc) + from (select array[i / 2] from generate_series(1,10) a(i)) b(ar); +select array_agg(ar) + from (select array_agg(array[i, i+1, i-1]) + from generate_series(1,2) a(i)) b(ar); + +-- array_agg(anyarray), varlena types +select array_agg(array[1.2,1.3,1.4]) from generate_series(1,3); +select array_agg(array['Hello','Hohoho','Hi']) from generate_series(1,3); + +-- array_agg(anyarray), arrays with nulls +select array_agg(array[i, null, i+1, null, i+2]) from generate_series(1,3) g(i); +select array_agg(array[1.1+ i, null, 1.1+i+1, null, 1.1+i+2]) from generate_series(1,3) g(i); +select array_agg(array[null, 'Hello','Hohoho', null,'Hi']) from generate_series(1,3); + +select array_agg(array[[null, 'Hello', null, 'Hi'],['Hello', null, 'Hi', null]]) from generate_series(1,2); + +-- errors +select array_agg('{}'::int[]) from generate_series(1,2); +select array_agg(null::int[]) from generate_series(1,2); +select array_agg(ar) + from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar); + -- multi-arg aggs, strict/nonstrict, distinct/order by select aggfstr(a,b,c) diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index fa8a20a..cb00f5f 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -432,6 +432,12 @@ select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by un select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; select array_agg(unique1) from tenk1 where unique1 < -15; +select array(select unique1 from tenk1 where unique1 < 15 order by unique1); +select array(select array[i,i/2] from generate_series(1,5) a(i)); +-- cannot accumulate null arrays and empty arrays +select array(select null::int[]); +select array(select '{}'::int[]); + select unnest(array[1,2,3]); select * from unnest(array[1,2,3]); select unnest(array[1,2,3,4.5]::float8[]);
script.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers