Re: array question

On 09/10/2009 17:17, Whit Armstrong wrote:
> Is there any easy way to get this data:
>
> kls_dev=# select * from ary_values;
> agent_name | myval
> ------------+-------
> a | 1
> a | 2
> a | 3
> b | 4
> b | 5
> b | 6
> (6 rows)
>
> to look like this:
>
> kls_dev=# select * from ary_test;
> agent_name | vals
> ------------+---------
> a | {1,2,3}
> b | {4,5,6}
> (2 rows)

Someone (Alvaro?) once posted a really handy aggregate which ought to do
what you want:

CREATE AGGREGATE array_accum(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);

And then you'd call it like so:

select agent_name, array_accum(myval) from ary_values
group by agent_name;

I hope that helps.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------