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

2 files changed, 43 insertions, 0 deletions

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;