Re: top-level DML under CTEs

2010/9/15 Hitoshi Harada <umi.tanuki@gmail.com>:
> 2010/9/15 Tom Lane <tgl@sss.pgh.pa.us>:
>>
>> Well, I would think that the no-duplication rule applies to each WITH
>> list separately, not both together.  If you do something like
>>
>> with t1 as (select * from foo)
>>  select * from
>>    (with t2 as (select * from foo)
>>       select * from t1, t2) ss;
>>
>
> 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.

regression=# with t as (select 1 as i) insert into z with t as(select
2 as i )values ((select * from t));
INSERT 0 1
Time: 1.656 ms
regression=# table z;
 f3
----
  2
(1 row)

Also, the sample Marko gave is OK.

> CREATE TABLE foo(a int);
>
> WITH t AS (SELECT * FROM foo)
> INSERT INTO bar
> WITH RECURSIVE foo (SELECT 1 AS a)
> SELECT * FROM t;
>

Hope this covers all the cases.

Regards,

-- 
Hitoshi Harada