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@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------