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
– John Powell, Commented Aug 11, 2014 at 12:41
I think you need to use array_cat for multidimensional array, see postgresql.org/docs/9.3/static/arrays.html. What are you hoping for as an end result? — John Powell
– John Powell, Commented Aug 11, 2014 at 13:04

Community · Accepted Answer · 2017-05-23 12:29:25Z

2

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 11, 2014 at 13:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gokul Over a year ago

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

Clodoaldo Neto · Accepted Answer · 2014-08-11 13:41:07Z

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, 2014 at 13:41

answered Aug 11, 2014 at 13:06

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

4 Comments

Gokul Over a year ago

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 Over a year ago

foo has a number of columns which i have omitted. any one of them could be bar.

Clodoaldo Neto Over a year ago

@Gokul So you need bar to be the first element of the array?

Gokul Over a year ago

I use bar only to select the groups of rows on which i need to apply the aggregate function.

jian · Accepted Answer · 2022-04-08 06:51:42Z

0

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 = '{}');

answered Apr 8, 2022 at 6:51

jian

5,0131 gold badge29 silver badges46 bronze badges

Collectives™ on Stack Overflow

appending to multi dimensional arrays in postgres

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related