CTE bug?
Folks,
While working to write the Sieve of Eratosthenes using CTEs, I ran
across a strange error, namely that it appears I'm not allowed to nest
WITH. Is this a bug?
Cheers,
David.
WITH RECURSIVE t1(n) AS (
VALUES(2)
UNION ALL
SELECT n+1 FROM t1 WHERE n < 1000
),
t2 (n, i) AS (
SELECT 2*n+2, 2
FROM t1 WHERE 2*n+2 <= 1000
UNION ALL
WITH t3(k) AS (
SELECT max(i) FROM t2
)
SELECT k*n+k, k
FROM
t1
CROSS JOIN
t3
WHERE k*n+k <= 1000
)
SELECT * FROM t1 EXCEPT SELECT n FROM t2 ORDER BY 1;
ERROR: syntax error at or near "WITH t3"
LINE 10: WITH t3(k) AS (
^
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
WITH RECURSIVE t1(n) AS (
VALUES(2)
UNION ALL
SELECT n+1 FROM t1 WHERE n < 1000
),
t2 (n, i) AS (
SELECT 2*n+2, 2
FROM t1 WHERE 2*n+2 <= 1000
UNION ALL
WITH t3(k) AS (
SELECT max(i) FROM t2
)
SELECT k*n+k, k
FROM
t1
CROSS JOIN
t3
WHERE k*n+k <= 1000
)
SELECT * FROM t1 EXCEPT SELECT n FROM t2 ORDER BY 1;
ERROR: syntax error at or near "WITH t3"
LINE 10: WITH t3(k) AS (
^
I think you need parentheses to have WITH attached to a member of a
UNION. In
WITH (...) SELECT x UNION SELECT y
I'm pretty sure the WITH attaches to the whole union, not the first
member.
regards, tom lane