Aggregate function for any array type
With the polymorphic type anyarray it works for all kinds of arrays, not just 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(arr) AS arr1 --> 1-dimensional array
,array_agg_mult(ARRAY[arr]) AS arr2 --> 2-dimensional array
,array_agg_mult(ARRAY[ARRAY[arr]]) AS arr3 --> 3-dimensional array
-- etc.
FROM (
VALUES
('{1,2,3}'::int[]) -- 1-dimensional array
,('{4,5,6}')
,('{7,8,9}'))
) x(arr);
Or:
SELECT array_agg_mult(arr) AS arr1 --> 2-dimensional array
FROM (
VALUES
('{{1,2,3}}'::int[]) -- 2-dimensional array
,('{{4,5,6}}')
,('{{7,8,9}}')
) x(arr);
All resulting columns are of the same type: int[].
