sql: support to array_agg() for arrays

[sean-]

Is your feature request related to a problem? Please describe.

Minimum reproduction:

        => SELECT version();
┌───────────────────────────────────────────────────────────────────────────────────────────┐
│                                          version                                          │
├───────────────────────────────────────────────────────────────────────────────────────────┤
│ CockroachDB CCL v22.2.17 (aarch64-unknown-linux-gnu, built 2023/11/20 16:09:42, go1.19.6) │
└───────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
        => CREATE TABLE __test_array_agg(a TEXT PRIMARY KEY, b TEXT, c TEXT);
CREATE TABLE
        => SELECT array_agg(array[a, b, c]) FROM __test_array_agg;
ERROR:  unknown signature: array_agg(string[])

Describe alternatives you've considered

I was trying to come up with workarounds and got a close approximation, but it returns record[] instead of text[] like postgres does.

select array_agg(t.*) from __test_array_agg as t;

Jira issue: CRDB-35334

[yuzefovich]

We already have a somewhat-related custom array_cat_agg which behaves differently from array_agg with arrays in PG, for example:

CREATE TABLE __test_array_agg(a TEXT PRIMARY KEY, b TEXT, c TEXT);
INSERT INTO __test_array_agg VALUES ('a', 'b', 'c'), ('aa', 'bb', 'cc'), ('aaa', 'bbb', 'ccc');

PG:

SELECT array_agg(array[a, b, c]) FROM __test_array_agg;
             array_agg              
------------------------------------
 {{a,b,c},{aa,bb,cc},{aaa,bbb,ccc}}

CRDB:

SELECT array_cat_agg(array[a, b, c]) FROM __test_array_agg;                                                                     
         array_cat_agg
--------------------------------
  {a,b,c,aa,bb,cc,aaa,bbb,ccc}

---

I do have a prototype in #117838 that would expand array_agg to support arrays as inputs. There is a minor complication that we, generally speaking, don't support multi-dimensional arrays (#32552), so it might be somewhat contentious on whether that change will get merged (evaluation of array_agg on arrays internally uses nested arrays as well as its result is a nested array).