Thread: Common table expression - parsing questions
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
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
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
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 withthe newest release) Thomas
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:The standard *requires* the keyword.2. Do you intend to remove the requirement to include the recursive keyword - as other vendors allow
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
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
(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