Re: Using values in an array in a subquery

On Wed, Nov 09, 2005 at 05:39:02PM +0000, David Orme wrote:
> I want to be able to aggregate a value for each row based on the
> values of var for the rows with node values appearing in the members
> array. If the aggregate was sum, then I'm looking to get something
> like this:
>
> node | sum
> -----+-----
> 1 |
> 2 |
> 3 |
> 4 |
> -3 | 3
> -2 | 8
> -1 | 10

Is this what you're looking for? It works for me in 7.4 and later,
at least with your test data.

SELECT a.node, sum(b.var)
FROM array_test AS a
LEFT OUTER JOIN array_test AS b ON b.node = ANY(a.members)
GROUP BY a.node;

node | sum
------+-----
-1 | 10
-2 | 8
-3 | 3
4 |
3 |
2 |
1 |
(7 rows)

--
Michael Fuhr