Re: text array accumulate to multidimensional text array

On Tue, Oct 14, 2008 at 9:22 AM, Rainer Zaiss <r(dot)zaiss(at)free(dot)fr> wrote:
> Dear list,
>
> I would like to aggregate a text array into a multidimensional text array.
>
> Let us say I have one table with two collumns
>
> ID ARRAY
> A {"A1","B1","C1"}
> A {"A2","B2","C2"}
> B {"A3","B3","C3"}
>
> If I use a GROUP BY ID, I would like to receive following result:
>
> ID ARRAY
> A {{"A1","B1","C1"},{"A2","B2","C2"}}
> B {{"A3","B3","C3"}}
>
> I searched around but I didn't find any solution
>

the easy way doesn't work because 'array_append' doesn't allow you to
create 2d arrays from 1d array.

the easy way that kinda sorta does what you want is like this:
CREATE AGGREGATE array_accum2 (anyarray)
(
sfunc = array_cat,
stype = anyarray,
initcond = '{}'
);

select key, array_accum2(values) from a group by key;

key | array_accum2
-----+---------------------
B | {A3,B3,C3}
A | {A1,B1,C1,A2,B2,C2}
(2 rows)

note the returned 1d array.

Probably the only way to do exactly what you want is a specialized C
function that works similarly to array_cat/array_append...it shouldn't
be too difficult to write. I may be missing something though.

merlin