Initial array in function to aggregate multi-dimensional array

Question

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?

Answer 1

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).

Answer 2

Using the built-in array_cat function works.

CREATE AGGREGATE array_sum2 (int[])  (
    SFUNC     = array_cat,
    STYPE     = int[],
    INITCOND  = '{}'
);

test:

select array_sum2(array[d.a]) from (select array[1,1,2,3] as a union select array[5,8,13,21] as a) d;
       array_sum2        
-------------------------
 {{1,1,2,3},{5,8,13,21}}