Re: Putting an aggregate value in an UPDATE statement...

On 02/03/2012 14:38, rajanski wrote:
> Okay I have a similar but seemingly more complicated Problem with aggregate
> values in an UPDATE statement:
>
> update test set ew_data_vm_fraction =
> array_agg(unnest(ew_data_vm) * area_percentage)
>
> gives me the obvious "ERROR: cannot use aggregate function in UPDATE"
> message
>
> when I set i in parentheses
>
> update test set ew_data_vm_fraction =
> (select array_agg(unnest(ew_data_vm) * area_percentage)
> from test
>
> it results in the message: "ERROR: set-valued function called in context
> that cannot accept a set"
>
> i am really desperate,what can I try else?
>
> Greetings!
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Putting-an-aggregate-value-in-an-UPDATE-statement-tp2140836p5531137.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>

Hi Rajanski

Your basic problem is you're not handling the set correctly. For one
thing you're multiplying a set by a number. Consider what happens if
you unnest one row (select the row as id=1):

SELECT unnest(ew_data_vm) from test WHERE id = 1;

This produces many rows and one column "unnest". But this is really
misleading. You select one row and get many. It would make much more
sense to write something like:

SELECT unnest FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );

...

SELECT unnest * area_percentage
FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );

...

SELECT array_agg(unnest * area_percentage)
FROM unnest( (SELECT ew_data_vm FROM test WHERE id =1) );

Then to update you get:

update test set ew_data_vm_fraction =
(select array_agg(unnest * area_percentage) FROM unnest(ew_data_vm) )

Hope this helps