Re: text array accumulate to multidimensional text array

On Wed, Oct 22, 2008 at 12:55 PM, Osvaldo Kussama
<osvaldo(dot)kussama(at)gmail(dot)com> wrote:
> 2008/10/14, Rainer Zaiss <r(dot)zaiss(at)free(dot)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