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
Show quoted text
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�