this example statement works fine in postgresql 9.1 and above. Unfortunately it does not work in older versions, the reason being that update does not support the with statement. How can I put the RETURNING in an array? I am writing a function using plpgsql.
begin
create table testing(
i serial,
t text);
insert into testing
(t)
values (null),(null),(null);
with abc AS (
update testing
set t = null
returning i )
select array_agg(i)
from abc;
rollback
Thank you for your help!
Unfortunately you can't do that on versions prior to 9.1, as writable common table expressions (A.K.A wCTE) was available only at 9.1 (see "Writable Common Table Expressions" on PostgreSQL feature matrix).
The only solution I can see is this case is doing this on your application or using a function, like:
CREATE OR REPLACE FUNCTION test()
RETURNS int[]
VOLATILE
LANGUAGE plpgsql AS $$
DECLARE
ret int[];
aux int;
BEGIN
ret := '{}';
FOR aux IN
UPDATE testing
SET t = null
RETURNING i
LOOP
ret := ret || aux;
END LOOP;
RETURN ret;
END;
$$;
In any case, if you are using a version older than that, I'd recommend you to upgrade to the newer version (now 9.3) as soon as possible. The 8.4 version is about to lost support, and 9.0 will lost next year (see versioning policy).
EDIT:
To left a reference for those using PostgreSQL 9.1+, the above can be done with wCTE as the following:
WITH updt AS (
UPDATE testing
SET t = null
RETURNING i
)
SELECT array_agg(i) FROM updt;
