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