array_agg aggregate function, as per SQL:2008, but without ORDER BY clause

Rearrange the documentation a bit now that array_agg and xmlagg have similar
semantics and issues.

best of Robert Haas, Jeff Davis, Peter Eisentraut

8 files changed, 95 insertions, 7 deletions

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index e28066dc687..354fd98811b 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -350,7 +350,7 @@ S094	Arrays of reference types			NO
 S095	Array constructors by query			YES	
 S096	Optional array bounds			YES	
 S097	Array element assignment			NO	
-S098	ARRAY_AGG			NO	
+S098	ARRAY_AGG			NO	ORDER BY clause not supported
 S111	ONLY in query expressions			YES	
 S151	Type predicate			NO	
 S161	Subtype treatment			NO	
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index be2068ac5dc..4eeb64dbd81 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -6,12 +6,13 @@
  * Copyright (c) 2003-2008, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.23 2008/01/01 19:45:52 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.24 2008/11/13 15:59:50 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
+#include "nodes/execnodes.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/lsyscache.h"
@@ -465,3 +466,34 @@ create_singleton_array(FunctionCallInfo fcinfo,
 	return construct_md_array(dvalues, NULL, ndims, dims, lbs, element_type,
 							  typlen, typbyval, typalign);
 }
+
+Datum
+array_agg_transfn(PG_FUNCTION_ARGS)
+{
+	Oid arg1_typeid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+
+	if (arg1_typeid == InvalidOid)
+		ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						errmsg("could not determine input data type")));
+
+	/* cannot be called directly because of internal-type argument */
+	Assert(fcinfo->context && IsA(fcinfo->context, AggState));
+
+	PG_RETURN_POINTER(accumArrayResult(PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0),
+									   PG_ARGISNULL(1) ? (Datum) 0 : PG_GETARG_DATUM(1),
+									   PG_ARGISNULL(1),
+									   arg1_typeid,
+									   ((AggState *) fcinfo->context)->aggcontext));
+}
+
+Datum
+array_agg_finalfn(PG_FUNCTION_ARGS)
+{
+	/* cannot be called directly because of internal-type argument */
+	Assert(fcinfo->context && IsA(fcinfo->context, AggState));
+
+	if (PG_ARGISNULL(0))
+		PG_RETURN_NULL();   /* returns null iff no input values */
+
+	PG_RETURN_ARRAYTYPE_P(makeArrayResult((ArrayBuildState *) PG_GETARG_POINTER(0), CurrentMemoryContext));
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 3e7e52b57f0..1f3d9c6485c 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.504 2008/11/12 13:09:27 petere Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.505 2008/11/13 15:59:50 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200811121
+#define CATALOG_VERSION_NO	200811131
 
 #endif
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index a8b4ac2f937..350f4968fcc 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.66 2008/03/27 03:57:34 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.67 2008/11/13 15:59:50 petere Exp $
  *
  * NOTES
  *	  the genbki.sh script reads this file and generates .bki
@@ -220,6 +220,9 @@ DATA(insert ( 2243 bitor		  -					0	1560	_null_ ));
 /* xml */
 DATA(insert ( 2901 xmlconcat2	  -					0	142		_null_ ));
 
+/* array */
+DATA(insert ( 2335	array_agg_transfn	array_agg_finalfn		0	2281	_null_ ));
+
 /*
  * prototypes for functions in pg_aggregate.c
  */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0e83606f926..5c01d1b3701 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.526 2008/11/12 13:38:04 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.527 2008/11/13 15:59:50 petere Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -1022,6 +1022,13 @@ DATA(insert OID = 1193 (  array_fill PGNSP PGUID 12 1 0 0 f f f f i 2 2277 "2283
 DESCR("array constructor with value");
 DATA(insert OID = 1286 (  array_fill PGNSP PGUID 12 1 0 0 f f f f i 3 2277 "2283 1007 1007" _null_ _null_ _null_ array_fill_with_lower_bounds _null_ _null_ _null_ ));
 DESCR("array constructor with value");
+DATA(insert OID = 2333 (  array_agg_transfn   PGNSP PGUID 12 1 0 0 f f f f i 2 2281 "2281 2283" _null_ _null_ _null_ array_agg_transfn _null_ _null_ _null_ ));
+DESCR("array_agg transition function");
+DATA(insert OID = 2334 (  array_agg_finalfn   PGNSP PGUID 12 1 0 0 f f f f i 1 2277 "2281" _null_ _null_ _null_ array_agg_finalfn _null_ _null_ _null_ ));
+DESCR("array_agg final function");
+DATA(insert OID = 2335 (  array_agg        PGNSP PGUID 12 1 0 0 t f f f i 1 2277 "2283" _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+DESCR("concatenate aggregate input into an array");
+
 DATA(insert OID = 760 (  smgrin			   PGNSP PGUID 12 1 0 0 f f t f s 1 210 "2275" _null_ _null_ _null_  smgrin _null_ _null_ _null_ ));
 DESCR("I/O");
 DATA(insert OID = 761 (  smgrout		   PGNSP PGUID 12 1 0 0 f f t f s 1 2275 "210" _null_ _null_ _null_  smgrout _null_ _null_ _null_ ));
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 33d9ad3207e..8b6ef08b276 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -49,7 +49,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/array.h,v 1.70 2008/11/12 13:09:28 petere Exp $
+ * $PostgreSQL: pgsql/src/include/utils/array.h,v 1.71 2008/11/13 15:59:50 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -275,4 +275,7 @@ extern ArrayType *create_singleton_array(FunctionCallInfo fcinfo,
 					   Datum element,
 					   int ndims);
 
+extern Datum array_agg_transfn(PG_FUNCTION_ARGS);
+extern Datum array_agg_finalfn(PG_FUNCTION_ARGS);
+
 #endif   /* ARRAY_H */
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 804d52b7982..1e990aff732 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1125,3 +1125,39 @@ select c, cardinality(c), d, cardinality(d) from arrtest;
  {foo,new_word}    |           2 | {bar,foo}     |           2
 (3 rows)
 
+select array_agg(unique1) from tenk1 where unique1 < 15;
+              array_agg               
+--------------------------------------
+ {4,2,1,6,14,9,8,5,3,13,12,11,7,10,0}
+(1 row)
+
+select array_agg(ten) from tenk1 where unique1 < 15;
+            array_agg            
+---------------------------------
+ {4,2,1,6,4,9,8,5,3,3,2,1,7,0,0}
+(1 row)
+
+select array_agg(nullif(ten, 4)) from tenk1 where unique1 < 15;
+               array_agg               
+---------------------------------------
+ {NULL,2,1,6,NULL,9,8,5,3,3,2,1,7,0,0}
+(1 row)
+
+select cardinality(array_agg(unique1)) from tenk1 where unique1 < 15;
+ cardinality 
+-------------
+          15
+(1 row)
+
+select array_agg(unique1) from (select * from tenk1 order by unique1 asc) as tab where unique1 < 15;
+              array_agg               
+--------------------------------------
+ {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
+(1 row)
+
+select array_agg(unique1) from tenk1 where unique1 < -15;
+ array_agg 
+-----------
+ 
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 04b19a4acea..586f65c2dd6 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -395,3 +395,10 @@ select array_length(array[[1,2,3], [4,5,6]], 3);
 select cardinality(array[1,2,3]);
 select cardinality(array[[1,2,3], [4,5,6]]);
 select c, cardinality(c), d, cardinality(d) from arrtest;
+
+select array_agg(unique1) from tenk1 where unique1 < 15;
+select array_agg(ten) from tenk1 where unique1 < 15;
+select array_agg(nullif(ten, 4)) from tenk1 where unique1 < 15;
+select cardinality(array_agg(unique1)) from tenk1 where unique1 < 15;
+select array_agg(unique1) from (select * from tenk1 order by unique1 asc) as tab where unique1 < 15;
+select array_agg(unique1) from tenk1 where unique1 < -15;