appending to multi dimensional arrays in postgres

Question

I am trying to create a multi-column aggregate function that needs to accumulate all the column values for later processing.

CREATE OR REPLACE FUNCTION accumulate_scores(prev integer[][],l1 integer,
l2 integer,l3 integer) RETURNS integer[][] AS
$$      
BEGIN  
prev[1] = array_append(prev[1],l1);
prev[2] = array_append(prev[2],l2);
prev[3] = array_append(prev[3],l3);
return prev;
END 
$$ LANGUAGE plpgsql;

CREATE AGGREGATE my_aggregate(integer,integer,integer)(
 SFUNC  = accumulate_scores,
 STYPE     = integer[][],
 INITCOND  = '{}'  
);

select accumulate_scores(ARRAY[1]|| ARRAY[[1],[1]],2,3,4);

I get this error

ERROR: function array_append(integer, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

How do i accumulate these values into a multi dimensional array?

Edit: i have tried with array_cat and get the same error. thought array_append might be right since prev[1] is a uni dimensional array

I believe it is because array_append takes array, element as parameters, and the prev[1] in array_append(prev[1],l1) is not an array but a value. — John Powell, Aug 11 '14 at 12:41
I think you need to use array_cat for multidimensional array, see http://www.postgresql.org/docs/9.3/static/arrays.html. What are you hoping for as an end result? — John Powell, Aug 11 '14 at 13:04

score 2 · Accepted Answer · edited May 23 '17 at 12:29

You function & aggregate function would "work" like this, but it would only create a 1-dimenstional array:

CREATE OR REPLACE FUNCTION accumulate_scores(prev integer[][]
                                            ,l1 integer
                                            ,l2 integer
                                            ,l3 integer)
  RETURNS integer[][] AS
$func$
BEGIN 
prev := prev || ARRAY[l1,l2,l3];

RETURN prev;
END 
$func$ LANGUAGE plpgsql;

CREATE AGGREGATE my_aggregate(integer,integer,integer)(
 SFUNC     = accumulate_scores,
 STYPE     = integer[][],
 INITCOND  = '{}'  
);

The declaration RETURNS integer[][] is equivalent to just RETURNS integer[]. For Postgres all arrays on the same base element are the same type. Additional brackets are for documentation only and ignored.

To actually produce a multi-dimensional array, you don't need a custom function. Just a custom aggregate function:

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

Then you can:

SELECT array_agg_mult(ARRAY[ARRAY[l1,l2,l3]])  -- note the two array layers!
from   tbl;

More details here:

Selecting data into a Postgres array

Thanks. I used approach 2 to get a multi-dimensional array which i process in the final function of my aggregate into multiple single arrays on which i can do further math — Gokul, Aug 11 '14 at 14:15

Clodoaldo Neto · Answer 2 · 2014-08-11T13:41:07.103

0

This is how I understand the question

select array[
    array_agg(l1),
    array_agg(l2),
    array_agg(l3)
]
from (values
    (1, 9, 1, 2, 3), (2, 9, 4, 5, 6)
) s(id, bar, l1, l2, l3)
group by bar;
        array        
---------------------
 {{1,4},{2,5},{3,6}}

edited Aug 11 '14 at 13:41

answered Aug 11 '14 at 13:06

Clodoaldo Neto

118,695
26
233
260

more like CREATE TABLE foo( id bigserial NOT NULL, l1 integer, l2 integer, l3 integer); select my_aggregate(l1,l2,l3) from foo where blah blah group by bar – Gokul Aug 11 '14 at 13:24
foo has a number of columns which i have omitted. any one of them could be bar. – Gokul Aug 11 '14 at 14:13
@Gokul So you need bar to be the first element of the array? – Clodoaldo Neto Aug 11 '14 at 14:14
I use bar only to select the groups of rows on which i need to apply the aggregate function. – Gokul Aug 11 '14 at 14:17

score 0 · Answer 3 · answered Apr 08 '22 at 06:51

In case somebody encount some issue following erwin's great answer.
before postgresql 14 follow Erwin's answer. for postgresql 14, we need tiny tweak.

create or replace aggregate 
array_agg_mult(anycompatiblearray)
(sfunc = array_cat, 
stype = anycompatiblearray, 
initcond = '{}');

appending to multi dimensional arrays in postgres

3 Answers3