The patch attached is based on the one rejected at the last CF for 9.0
last year.
http://archives.postgresql.org/message-id/16303.1266023203@sss.pgh.pa.us
This patch implements the feature that allows top-level DMLs under CTE
WITH clause. For example:
WITH t AS (SELECT * FROM x)
UPDATE y SET val = t.val FROM t
WHERE y.key = t.key;
This feature is part of writeable CTEs proposed by David Fetter originally.
There were two issues at the CF.
1. WITH clause atop INSERT
Although the previous discussion got the consensus that we forbid WITH
atop INSERT, it seems to me that it can be allowed. I managed to do it
by treating the top WITH clause (of INSERT) as if the one of SELECT
(or VALUES). It is possible to disallow the CTE over INSERT statement,
but the lack for INSERT, though there are for UPDATE and DELETE,
sounds inconsistent enough.
2. OLD/NEW in rules
Following the subsequent discussion after the post linked above, I add
code to throw an appropriate error when OLD/NEW is used in WITH
clauses. It is true that OLD/NEW references look sane to general
users, but actually (at least in our implementation) they are located
in the top-level query's Range Table List. Consequently, they are
invisible inside the WITH clause. To allow them, we should rewrite the
rule systems overall. Thus, we forbid them in WITH though we should
throw an error indicating appropriate message.
I'll add the entry to CF app later. Any feedback is welcome.
Regards,
--
Hitoshi Harada