Thread: array question

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)

In other words, I would like to group by 'agent_name' and then convert
all the 'myvals' associated w/ that agent_name into an array.

I tried a few simple things like this:
kls_dev=# select array_append(myval) from ary_values group by agent_name;

but no luck so far.

Thanks,
Whit

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

That's exactly what I needed.   Thanks very much!

-Whit


On Fri, Oct 9, 2009 at 12:29 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> 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
> ------------------------------------------------------------------
>

Whit Armstrong <armstrong.whit@gmail.com> wrote:

> That's exactly what I needed.   Thanks very much!

If you have 8.4, you can use the build-in function array_agg() instead:

test=*# select * from x;
 a | b
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 4
(5 Zeilen)

Zeit: 0,203 ms
test=*# select a, array_agg(b) from x group by a;
 a | array_agg
---+-----------
 1 | {1,2,3}
 2 | {1,4}
(2 Zeilen)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°