Re: top-level DML under CTEs

2010/9/23 Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>:
> On 2010-09-17 4:48 AM, Hitoshi Harada wrote:
>>
>> 2010/9/15 Hitoshi Harada<umi(dot)tanuki(at)gmail(dot)com>:
>>>
>>> Well, I didn't know it is allowed. That would look like the way to go.
>>
>> I made changes to the previous version, so that it avoids to resolve
>> CTE name duplication.
>
> This patch still doesn't address the issue Tom raised here:
> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00753.php
>
> For WITH .. INSERT .. WITH .. SELECT ..; this patch works OK, but not so
> much for VALUES:
>
> =# CREATE RULE barrule AS ON UPDATE TO bar DO INSTEAD
> -# WITH RECURSIVE t AS (SELECT -1)
> -# INSERT INTO bar
> -# WITH t AS (SELECT 1)
> -# VALUES((SELECT * FROM t));
> CREATE RULE
>
> =# \d bar
>      Table "public.bar"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
> Rules:
>    barrule AS
>    ON UPDATE TO bar DO INSTEAD  WITH RECURSIVE t AS (
>         SELECT 1
>        ), t AS (
>         SELECT (-1)
>        )
>  INSERT INTO bar (a)  WITH RECURSIVE t AS (
>                 SELECT 1
>                ), t AS (
>                 SELECT (-1)
>                )
>
>  VALUES (( SELECT t."?column?"
>           FROM t))

I ran the sql and recognized what is wrong. In VALUES case, the WITH
table "t" is copied in one list and shown up in the both of
INSERT-level WITH and SELECT-level WITH. Since the transformation of
WITH clause to cheat postgres is in the parser stage currently, I
wonder if this should be done in the rewriter or the planner stage.

Thanks for the report. Next time, please point the clear problem in
English aside the sample.

Regards,

--
Hitoshi Harada