On 25th of February, Tom Lane committed patch:
Support data-modifying commands (INSERT/UPDATE/DELETE) IN WITH.
This patch implements data-modifying WITH queries according TO the
semantics that the updates ALL happen WITH the same command counter VALUE,
AND IN an unspecified ORDER. Therefore one WITH clause can't see the
effects of another, nor can the outer query see the effects other than
through the RETURNING values. And attempts to do conflicting updates will
have unpredictable results. We'll need TO document ALL that.
This commit just fixes the code; documentation updates are waiting ON
Marko Tiikkaja AND Hitoshi Harada
Continue reading Waiting for 9.1 – Writable CTE
In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.
Unfortunately it could not be used as source of rows for anything in sql.
INSERT INTO table_backup DELETE FROM TABLE WHERE ... returning *;
Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:
Allow SQL-LANGUAGE functions TO RETURN the output OF an INSERT/UPDATE/DELETE
RETURNING clause, NOT just a SELECT AS formerly.
A side effect OF this patch IS that WHEN a set-returning SQL FUNCTION IS used
IN a FROM clause, performance IS improved because the output IS collected INTO
a tuplestore WITHIN the FUNCTION, rather than USING the less efficient
Continue reading Waiting for 8.4 – sql-wrappable RETURNING