Learning SQL: nested CTE and UNION

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

Responses

Browse pgsql-hackers by date

  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

Browse pgsql-novice by date

  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