Re: top-level DML under CTEs

2010/9/15 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2010/9/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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