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