Skip site navigation (1) Skip section navigation (2)

Re: top-level DML under CTEs

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: top-level DML under CTEs
Date: 2010-09-17 01:48:35
Message-ID: AANLkTi=MqLZxc4yrFQpNRL53C6vZyUBrYCW67jMOcHyk@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-rrreviewers
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

Attachment: toplevel-dml-cte.20100917.patch
Description: application/octet-stream (20.7 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2010-09-17 02:00:26
Subject: Re: [BUGS] BUG #5305: Postgres service stops when closing Windows session
Previous:From: Robert HaasDate: 2010-09-17 01:23:53
Subject: Re: BUG #5650: Postgres service showing as stopped when in fact it is running

pgsql-rrreviewers by date

Next:From: David FetterDate: 2010-09-17 16:53:47
Subject: client authentication hook
Previous:From: Greg SmithDate: 2010-09-16 19:49:46
Subject: Re: Day 01/31

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group