Обсуждение: ERROR: cannot use aggregate function in RETURNING (in UPDATE)
The pg UPDATE manpage states that... The syntax of the RETURNING list is identical to that of the output list of SELECT. (http://www.postgresql.org/docs/9.2/static/sql-update.html) But when I use it inside a function such as: create or replace function foo() returns void as $$ declare keys date[]; begin update fin set div = div where div < 0.02 returning array_agg(d) as keys; raise notice 'updated these %', keys; -- use the returned keys... end; $$ language 'plpgsql'; And then run, in psql, select * from foo(); I get an error saying cannot use aggregate function in RETURNING. Is this a bug or a feature? The fin table in question is a very tiny test table: create table fin ( dt date, div real ); Will appreciate your help. What I want is the list of certain key columns from the rows that were updated. Thanks Dinesh -- View this message in context: http://postgresql.1045698.n5.nabble.com/ERROR-cannot-use-aggregate-function-in-RETURNING-in-UPDATE-tp5749475.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
dinesh <dinesh@vssod.com> writes: > I get an error saying cannot use aggregate function in RETURNING. > Is this a bug or a feature? Well, it's not a bug. RETURNING is defined as returning one row per row inserted/updated/deleted, so aggregates are nonsensical there. In recent versions you could put the UPDATE RETURNING inside a WITH, and then do the aggregation in a SELECT over that. regards, tom lane
Thanks Tom, that worked out great.
--
Regards
Dinesh
From: Tom Lane-2 [via PostgreSQL] [mailto:[hidden email]]
Sent: Sunday, March 24, 2013 3:26 PM
To: dinesh
Subject: Re: ERROR: cannot use aggregate function in RETURNING (in UPDATE)
dinesh <[hidden email]> writes:
> I get an error saying cannot use aggregate function in RETURNING.
> Is this a bug or a feature?
Well, it's not a bug. RETURNING is defined as returning one row per
row inserted/updated/deleted, so aggregates are nonsensical there.
In recent versions you could put the UPDATE RETURNING inside a WITH,
and then do the aggregation in a SELECT over that.
regards, tom lane
--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
click here.
NAML
View this message in context: RE: ERROR: cannot use aggregate function in RETURNING (in UPDATE)
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.