Resp.: text array accumulate to multidimensional text array

2008/10/14, Rainer Zaiss <r.zaiss@free.fr>:
>
> 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
>


Try:

bdteste=# CREATE OR REPLACE FUNCTION array_cat1(p1 anyarray, p2
anyarray) RETURNS anyarray AS $$
bdteste$# BEGIN
bdteste$#    IF  p1 = '{}'::text[] THEN
bdteste$#       RETURN(ARRAY[p2]);
bdteste$#    ELSE
bdteste$#       RETURN(ARRAY_CAT(p1, p2));
bdteste$#    END IF;
bdteste$# END;
bdteste$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
bdteste=# CREATE AGGREGATE array_accum3(anyarray)
(
   sfunc = array_cat1,
   stype = anyarray,
   initcond = '{}'
);
CREATE AGGREGATE
bdteste=# CREATE TEMP TABLE foo(
bdteste(#    id   char(1),
bdteste(#    a    text[]);
CREATE TABLE
bdteste=# INSERT INTO foo VALUES('A', '{"A1","B1","C1"}');
INSERT 0 1
bdteste=# INSERT INTO foo VALUES('A', '{"A2","B2","C2"}');
INSERT 0 1
bdteste=# INSERT INTO foo VALUES('B', '{"A3","B3","C3"}');
INSERT 0 1
bdteste=# SELECT * FROM foo;
 id |     a
----+------------
 A  | {A1,B1,C1}
 A  | {A2,B2,C2}
 B  | {A3,B3,C3}
(3 registros)

bdteste=# SELECT id, array_accum3(a) FROM foo GROUP BY id;
 id |      array_accum3
----+-------------------------
 B  | {{A3,B3,C3}}
 A  | {{A1,B1,C1},{A2,B2,C2}}
(2 registros)

Osvaldo