Re: Using values in an array in a subquery

On 10 Nov 2005, at 18:15, Michael Fuhr wrote:

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

That's fantastic - exactly what I was looking for.

Many thanks,
David




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