Support arrays as input to array_agg() and ARRAY(SELECT ...).

These cases formerly failed with errors about "could not find array type
for data type".  Now they yield arrays of the same element type and one
higher dimension.

The implementation involves creating functions with API similar to the
existing accumArrayResult() family.  I (tgl) also extended the base family
by adding an initArrayResult() function, which allows callers to avoid
special-casing the zero-inputs case if they just want an empty array as
result.  (Not all do, so the previous calling convention remains valid.)
This allowed simplifying some existing code in xml.c and plperl.c.

Ali Akbar, reviewed by Pavel Stehule, significantly modified by me

2 files changed, 85 insertions, 0 deletions

diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 46eff67b428..cb606afd9cf 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1497,6 +1497,7 @@ select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]);
            8
 (1 row)
 
+-- array_agg(anynonarray)
 select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
               array_agg               
 --------------------------------------
@@ -1521,6 +1522,55 @@ select array_agg(unique1) from tenk1 where unique1 < -15;
  
 (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)
+
+select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i);
+                  array_agg                  
+---------------------------------------------
+ {{2.2,2.3,2.4},{3.2,3.3,3.4},{4.2,4.3,4.4}}
+(1 row)
+
+select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i);
+             array_agg             
+-----------------------------------
+ {{Hello,9},{Hello,10},{Hello,11}}
+(1 row)
+
+select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i);
+              array_agg               
+--------------------------------------
+ {{1,1,2},{2,2,3},{3,NULL,4},{4,4,5}}
+(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 arrays of different dimensionality
 select unnest(array[1,2,3]);
  unnest 
 --------
@@ -1660,6 +1710,19 @@ select array_replace(array['AB',NULL,'CDE'],NULL,'12');
  {AB,12,CDE}
 (1 row)
 
+-- array(select array-value ...)
+select array(select array[i,i/2] from generate_series(1,5) i);
+              array              
+---------------------------------
+ {{1,0},{2,1},{3,1},{4,2},{5,2}}
+(1 row)
+
+select array(select array['Hello', i::text] from generate_series(9,11) i);
+               array               
+-----------------------------------
+ {{Hello,9},{Hello,10},{Hello,11}}
+(1 row)
+
 -- Insert/update on a column that is array of composite
 create temp table t1 (f1 int8_tbl[]);
 insert into t1 (f1[5].q1) values(42);
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index fa8a20ad1c0..733c19bed8d 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -427,11 +427,29 @@ select cardinality('{{1,2}}'::int[]);
 select cardinality('{{1,2},{3,4},{5,6}}'::int[]);
 select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]);
 
+-- array_agg(anynonarray)
 select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
 select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
 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;
 
+-- 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);
+select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i);
+select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i);
+select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i);
+-- 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);
+
 select unnest(array[1,2,3]);
 select * from unnest(array[1,2,3]);
 select unnest(array[1,2,3,4.5]::float8[]);
@@ -452,6 +470,10 @@ select array_replace(array['A','B','DD','B'],'B','CC');
 select array_replace(array[1,NULL,3],NULL,NULL);
 select array_replace(array['AB',NULL,'CDE'],NULL,'12');
 
+-- array(select array-value ...)
+select array(select array[i,i/2] from generate_series(1,5) i);
+select array(select array['Hello', i::text] from generate_series(9,11) i);
+
 -- Insert/update on a column that is array of composite
 
 create temp table t1 (f1 int8_tbl[]);