Обсуждение: 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°