Обсуждение: 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 WHEREk*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. InWITH (...) SELECT x UNION SELECT y I'm pretty sure the WITH attaches to the whole union, not the first member. regards, tom lane