2010/9/23 Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>:
> On 2010-09-17 4:48 AM, Hitoshi Harada wrote:
>>
>> 2010/9/15 Hitoshi Harada<umi.tanuki@gmail.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