From: | Adam Mackler <adammackler(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Learning SQL: nested CTE and UNION |
Date: | 2012-07-31 13:49:27 |
Message-ID: | 20120731134927.GA92750@bk.macklerlaw.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-novice |
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 will not work?
Thanks very much.
--
Adam Mackler
From | Date | Subject | |
---|---|---|---|
Next Message | Jonatan Reiners | 2012-07-31 13:57:40 | Re: Learning SQL: nested CTE and UNION |
Previous Message | Thom Brown | 2012-07-31 13:06:16 | JSON function reference in docs |
From | Date | Subject | |
---|---|---|---|
Next Message | Jonatan Reiners | 2012-07-31 13:57:40 | Re: Learning SQL: nested CTE and UNION |
Previous Message | Thomas Kellerer | 2012-07-31 13:15:31 | Re: problem with pg_dump |