Initial array in function to aggregate multi-dimensional array

aggregate-functions, arrays, multidimensional-array, postgresql

Solution

Postgres 9.5 or newer

... ships with an additional variant of the aggregate function `array_agg()`. The manual:

input arrays concatenated into array of one higher dimension (inputs must all have same dimensionality, and cannot be empty or null)

So not exactly the same as the custom aggregate function `array_agg_mult()` below. But use it, if you can. It's faster.

Related:

- How to sort two dimensional int array in PostgreSQL?

Postgres 9.4 or older

Aggregate function for any array type

With the polymorphic type `anyarray` it works for all kinds of arrays (including `integer[]`):

CREATE AGGREGATE array_agg_mult (anyarray) (
   SFUNC     = array_cat
 , STYPE     = anyarray
 , INITCOND  = '{}'
);

As @Lukas provided, the custom function `arrayappend()` is not needed. The built in `array_cat()` does the job. However, that doesn't explain why your example fails, while the one in Lukas' answer works. The relevant difference is that Lukas nested the array into another array layer with `array[d.a]`.

You trip over the incorrect assumption that you could declare a type `int[][]`. But you cannot: `int[][]` is the same type as `int[]` for the PostgreSQL type system. The chapter on array types in the manual explains:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in `CREATE TABLE` is simply documentation; it does not affect run-time behavior.

An `n`-dimensional integer array effectively is an array of `n-1`-dimensional arrays of integer in PostgreSQL. You can't tell that from the type which only defines the base element. You have to ask `array_dims()` to get the specifics.

To demonstrate:

SELECT array_agg_mult(arr1)               AS arr1  --> 1-dim array
     , array_agg_mult(ARRAY[arr1])        AS arr2  --> 2-dim array
     , array_agg_mult(ARRAY[ARRAY[arr1]]) AS arr3  --> 3-dim array
       -- etc.
FROM  (
   VALUES
      ('{1,2,3}'::int[])                           -- 1-dim array
    , ('{4,5,6}')
    , ('{7,8,9}')
   ) t(arr1);

Or:

SELECT array_agg_mult(arr2)        AS arr2  --> 2-dim array
     , array_agg_mult(ARRAY[arr2]) AS arr3  --> 3-dim array
     , array_agg(arr2)             AS arr3  --> 3-dim array; superior in Postgres 9.5+
FROM  (
   VALUES
      ('{{1,2,3}}'::int[])                  -- 2-dim array
     ,('{{4,5,6}}')
     ,('{{7,8,9}}')
   ) t(arr2);

All resulting columns are of the same type: `int[]` (even though containing a different number of dimensions).

Problem

I have a table with arrays of integer. I want to create an aggregate function that will return a 2-dimensional array with all the rows together. It then gets passed to `plr` to do some maths on it. I have: ``` CREATE OR REPLACE FUNCTION arrayappend(left int[][], right int[]) RETURNS int[] AS $BODY$ SELECT $1 || $2 ; $BODY$ LANGUAGE SQL; ``` and: ``` CREATE AGGREGATE array_sum2 (int[]) ( SFUNC = arrayappend, STYPE = int[][], INITCOND = '{}' ); ``` But the return type is `int[]`, not `int[][]`? How can I initialize the aggregate with an empty two dimensional array of integer?

Original source