Initial array in a function to aggregate a multidimensional array

Question

I have a table with integer arrays.

I want to create an aggregate function that will return a 2-dimensional array with all rows. It is then passed to plr to perform some mathematical calculations.

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 to initialize an aggregate using an empty two-dimensional integer array?

Answer 1

Postgres 9.5 sends an additional variant of the aggregate function array_agg() , which can aggregate arrays into an array from an array of one higher dimension. Cm:

• How to sort a two-dimensional int array in PostgreSQL?

---

Aggregation function for any type of array

With the polymorphic type anyarray it works for all types of arrays, not just integer :

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

As pointed out by @Lukas, the custom arrayappend() function is not needed. The built-in array_cat() function does the job. However, this does not explain why your example fails while the answer in @Lukas works. The corresponding difference is that @Lukas nested the array in another array with array[da] .

You are straying from the incorrect assumption that you can declare an int[][] . But you cannot: int[][] is the same type as int[] for a system like PostgreSQL. The chapter on array types in the manual explains:

The current implementation does not apply the declared number of sizes either. Arrays of a certain type of element - all are considered of the same type, regardless of size or quantity. Overall dimensions. So, declaring an array size or number of dimensions in a CREATE TABLE is just documentation; This does not affect runtime behavior.

An n dimensional array of integers is effectively an array of n-1 dimensional integer arrays in PostgreSQL. You cannot say this by type that defines only the base element . You must 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[] .

Answer 2

The built-in array_cat function works.

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

Test:

 select array_sum2(array[da]) 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}}