text array accumulate to multidimensional text array

On Tue, Oct 14, 2008 at 9:22 AM, Rainer Zaiss <r.zaiss@free.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

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

On Wed, Oct 22, 2008 at 12:55 PM, Osvaldo Kussama
<osvaldo.kussama@gmail.com> wrote:

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"}}

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;

very nice....I had a feeling there was a better way. For posterity,
here's a sql version:

CREATE OR REPLACE FUNCTION array_cat1(p1 anyarray, p2 anyarray)
RETURNS anyarray AS $$
select case when $1 = '{}'::text[] then array[$2] else array_cat($1, $2) end;
$$ language sql immutable;

No pl/pgsql dependency and it might be a tiny bit faster. Also, it
should be declared immutable.

merlin