Waiting for 9.5 – Support arrays as input to array_agg() and ARRAY(SELECT …).

On 25th of November, Tom Lane committed patch:

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

I have waited for this for so long. To be honest, I think our (PostgreSQL) array support is somewhat limited. But maybe I'm spoiled by Perl or Ruby.

In any way – up to now, there was no simple way to aggregate arrays – of course you could write your own aggregates, but this gets tedious fast.

Now, we can:

$ CREATE TABLE i (a int4[]);
CREATE TABLE
 
$ INSERT INTO i (a) VALUES ('{1,2}'), ('{3,4}'), ('{5,6}');
INSERT 0 3
 
$ SELECT array_agg(a) FROM i;
      array_agg      
---------------------
 {{1,2},{3,4},{5,6}}
(1 ROW)

You can also “run wild" with it, and aggregate 2 dimensional arrays further:

$ WITH x AS (SELECT array_agg(a) AS q FROM i)
SELECT array_agg(q) FROM x , generate_series(1,3);
                           array_agg                           
---------------------------------------------------------------
 {{{1,2},{3,4},{5,6}},{{1,2},{3,4},{5,6}},{{1,2},{3,4},{5,6}}}
(1 ROW)

Of course it will work with other base types too.

Great stuff, and thanks guys.

4 thoughts on “Waiting for 9.5 – Support arrays as input to array_agg() and ARRAY(SELECT …).”

  2. @Andreas:

    $ INSERT INTO i (a) VALUES ('{1}'), ('{2,3}'), ('{4,5,6}');
INSERT 0 3
 
$ SELECT array_agg(a) FROM i;
ERROR:  cannot accumulate arrays OF different dimensionality

    So yeah, it’s not perfect. But at least it’s a step in right direction.

  3. Work with different length is not simple due possible performance (memory) issues. Next, there is not clean how to merge arrays of different size – PostgreSQL arrays have to be regular. If you need a array of arrays, then use a trick ARRAY(ROW(ARRAY))

  4. I’ve came across slightly different use case recently, I wanted

    {1,2,3,4,5,6}

    for the given example to be returned.

    Done via:

    CREATE AGGREGATE _array_agg(anyarray) (
    SFUNC = array_cat, STYPE = anyarray);

Comments are closed.