pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCommon table expression - parsing questions
5+ messages / 4 participants
[nested] [flat]
* Common table expression - parsing questions
@ 2009-09-29 02:54 the6campbells <the6campbells@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: the6campbells @ 2009-09-29 02:54 UTC (permalink / raw)
To: pgsql-sql
Couple of questions:
1. Why does Postgres not throw a parsing error during sqlPrepare for this
statement vs at sqlExecute
with t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 union all
select c1, ctr + 1 from t_cte where c1=2 and ctr < 5)
select c1, ctr from t_cte
2. Do you intend to remove the requirement to include the recursive keyword
- as other vendors allow
3. Is it a documented restriction that you can only have one reference to
the CTE .. see above example which fails while this modified version works.
The former
works in other vendors.
with recursive t_cte ( c1, ctr ) as (
select 1,0 from tversion union
select 2,0 from tversion union all
select c1, ctr + 1 from t_cte where c1=1 and ctr < 5 )
select c1, ctr from t_cte
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Common table expression - parsing questions
@ 2009-10-04 06:10 Thomas Kellerer <spam_eater@gmx.net>
parent: the6campbells <the6campbells@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Thomas Kellerer @ 2009-10-04 06:10 UTC (permalink / raw)
To: pgsql-sql
the6campbells wrote on 29.09.2009 04:54:
> 2. Do you intend to remove the requirement to include the recursive
> keyword - as other vendors allow
The standard *requires* the keyword.
As far as I can tell there are two DBMS that require it (Postgres, Firebird) and two that don't (SQL Server and Oracle with the newest release)
Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Common table expression - parsing questions
@ 2009-10-04 13:25 the6campbells <the6campbells@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: the6campbells @ 2009-10-04 13:25 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
the db2 family does not
On Sun, Oct 4, 2009 at 2:10 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> the6campbells wrote on 29.09.2009 04:54:
>
>> 2. Do you intend to remove the requirement to include the recursive
>> keyword - as other vendors allow
>>
>
> The standard *requires* the keyword.
> As far as I can tell there are two DBMS that require it (Postgres,
> Firebird) and two that don't (SQL Server and Oracle with the newest release)
>
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Common table expression - parsing questions
@ 2009-10-04 15:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2009-10-04 15:00 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Thomas Kellerer <spam_eater@gmx.net> writes:
> the6campbells wrote on 29.09.2009 04:54:
>> 2. Do you intend to remove the requirement to include the recursive
>> keyword - as other vendors allow
> The standard *requires* the keyword.
The reason the standard requires the keyword is that the WITH-name
scoping rules are different in the two cases. This means it is possible
to construct 100% valid queries that mean different things depending on
whether RECURSIVE is present or not. Admittedly, they'd be uncommon
corner cases, but the short answer is that an implementation that does
not require the keyword will fail to process some standard-conforming
queries in a standard-conforming way.
IOW, no, we are not going to change this.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Common table expression - parsing questions
@ 2009-10-05 00:44 Lew <noone@lwsc.ehost-services.com>
parent: the6campbells <the6campbells@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Lew @ 2009-10-05 00:44 UTC (permalink / raw)
To: pgsql-sql
(top-posting corrected)
Thomas Kellerer wrote:
>> The standard *requires* the keyword.
the6campbells wrote:
> the db2 [sic] family does not
Ergo DB2 is not standard-compliant in that regard.
--
Lew
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-10-05 00:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-29 02:54 Common table expression - parsing questions the6campbells <the6campbells@gmail.com>
2009-10-04 06:10 ` Thomas Kellerer <spam_eater@gmx.net>
2009-10-04 13:25 ` the6campbells <the6campbells@gmail.com>
2009-10-05 00:44 ` Lew <noone@lwsc.ehost-services.com>
2009-10-04 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox