The following bug has been logged online:
Bug reference: 5987
Logged by: Daniel Grace
Email address: dgrace@wingsnw.com
PostgreSQL version: 9.1-alpha5
Operating system: Win7 x64
Description: Rows created by WITH (INSERT ... RETURNING) are not
visible to the rest of the query
Details:
This may not be a bug, but I'll describe it anyways. Apologies if this is a
duplicate -- "WITH" is apparently a stopword and searching the archives
using it produces zero results.
9.1 adds the ability to use WITH in INSERT/UPDATE/DELETE in both
directions.
However, the actual effects of the query in the WITH clause are not visible
to the outer query.
-----
DROP TABLE IF EXISTS a;
CREATE TABLE a ( t TEXT );
INSERT INTO a VALUES ('test1') RETURNING *; -- Works as expected
WITH b AS (INSERT INTO a VALUES ('test2') RETURNING *) SELECT * FROM b; --
Works as expected
WITH b AS (INSERT INTO a VALUES ('test3') RETURNING *) SELECT a.* FROM a
INNER JOIN b USING(t); -- Does not see the newly created row.
SELECT * FROM a WHERE t='test3'; -- But it was created.
WITH b AS (INSERT INTO a VALUES ('test4') RETURNING *) -- Does not see the
newly created row, thus the update does not happen.
UPDATE a SET t='test5'
FROM b
WHERE a.t=b.t;
SELECT * FROM a;
-----
This is also true if the WITH query is a stored procedure that modifies the
database and returns results, i.e. WITH b AS (SELECT * FROM
create_row('test6'))...
Presumably it affects UPDATE and DELETE as well, but I didn't test those
cases.
My actual use case is: I'm calling a function to duplicate+modify some rows.
(Essentially, it does INSERT ... SELECT from the same table, but forcing
the primary key to be reassigned via being a serial column and some other
changes). This function returns the new rows as results (doing RETURN QUERY
INSERT ... SELECT ... RETURNING *). In some situations, I want to further
update the freshly created rows, so the goal was to do this:
WITH newrows AS (SELECT * FROM function_that_creates_rows(...))
UPDATE basetable SET foo=overrides.bar
FROM newrows
LEFT JOIN (VALUES (...)) AS overrides(...)
WHERE ...
