Thread: 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

---

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.