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 author. Marko Tiikkaja and Hitoshi Harada
Ever since we got 8.2, we have this brilliant thing called “RETURNING", which lets you get back information from queries that modify data (delete/update/insert).
For example you could:
DELETE FROM table WHERE status = 'inactive' RETURNING *;
And then use returned values for example to backup them.
One thing that wasn't possible, was to use the values in some other query.
But now, thanks to wCTE it's possible. Finally. And you can, for example do things like:
WITH deleted_posts AS ( DELETE FROM posts WHERE created < now() - '6 months'::INTERVAL RETURNING * ) SELECT user_id, count(*) FROM deleted_posts group BY 1;
Which would delete all posts older that 6 months, and show list of affected users, with count of removed posts.
Great thing is that you can use the CTE for next insert/update/delete – for example like this:
WITH deleted_posts AS ( DELETE FROM posts WHERE created < now() - '6 months'::INTERVAL RETURNING * ), deleted_per_user as ( SELECT user_id, count(*) FROM deleted_posts GROUP BY 1 ) UPDATE counts SET posts_count = posts_count - d.count FROM deleted_per_user d WHERE d.user_id = counts.user_id;
Which does the delete, generates summary of deleted posts per user, and updates
additional table, using single query – and not single query for every user, or
single query for every deleted post (like it would happen with triggers).
This is absolutely amazing. As a side benefit, it will provide answer to all
those people that “need" a way to update two tables with the same query.