Hi:
I'm trying to learn SQL, using PostgreSQL 9.1.3. I would like to understand some behavior that strikes me as
inconsistent.To wit:
This works:
WITH innermost AS (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3;
I get this:
?column?
----------
2
3
(2 rows)
This works:
WITH outmost AS (
(WITH innermost AS (SELECT 2)
SELECT * FROM innermost)
)
SELECT * FROM outmost;
Result:
?column?
----------
2
(1 row)
This also works:
WITH outmost AS (
SELECT 1
UNION (WITH innermost AS (SELECT 2)
SELECT * FROM innermost)
)
SELECT * FROM outmost;
I get this:
?column?
----------
1
2
(2 rows)
But this does not work:
WITH outmost AS (
SELECT 1
UNION (WITH innermost as (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3)
)
SELECT * FROM outmost;
Result:
ERROR: relation "innermost" does not exist
LINE 4: SELECT * FROM innermost
To my way of thinking, either the last one should succeed or one of the other ones should fail. I don't see the
pattern.Is there some general rule that would enable me to predict what combinations of nested CTEs and UNIONs will or
willnot work?
Thanks very much.
--
Adam Mackler