Re: top-level DML under CTEs

2010/9/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> On 2010-09-14 10:51 PM, Tom Lane wrote:
>>> My recollection is that whether a CTE is marked RECURSIVE or not affects
>>> its scope of visibility, so that confusing the two cases can result in
>>> flat-out incorrect parser behavior.
>
>> The worst I can think of is:
>
>> CREATE TABLE foo(a int);
>
>> WITH t AS (SELECT * FROM foo)
>> INSERT INTO bar
>> WITH RECURSIVE foo (SELECT 1 AS a)
>> SELECT * FROM t;
>
>> t will actually be populated with the results of the CTE, not the table foo.
>
>> I don't think this is a huge problem in real life, but if someone thinks
>> otherwise, I think we could just error out if the lists have a different
>> RECURSIVE definition.
>
> Wrong is wrong.  Doesn't matter whether it's "a huge problem in real life".
>
> Why is it so difficult to do this correctly?

Because INSERT INTO ... (SELECT|VALUES) is already a collection of
kludge (as comments say). It was possible to parse the two WITHs
separately, but it results in ambiguous naming issue;
parseWithClause() asserts there's only one WITH clause in the Stmt and
detects duplicated CTE name in it. It seems possible to call
parseWithClause() twice by cheating ParseState and to try to find name
duplication outside it, though it is another kludge :-(

Now that we find the worst situation, I start to think I have to take
the kludy way anyway.

Regards,

--
Hitoshi Harada