Learning SQL: nested CTE and UNION

Поиск
Список
Период
Сортировка
От Adam Mackler
Тема Learning SQL: nested CTE and UNION
Дата
Msg-id 20120731134927.GA92750@bk.macklerlaw.com
обсуждение исходный текст
Ответы Re: Learning SQL: nested CTE and UNION  (Jonatan Reiners <jreiners@encc.de>)
Re: Learning SQL: nested CTE and UNION  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
willnot work? 

Thanks very much.

--
Adam Mackler

В списке pgsql-novice по дате отправления:

Предыдущее
От: Thomas Kellerer
Дата:
Сообщение: Re: problem with pg_dump
Следующее
От: Jonatan Reiners
Дата:
Сообщение: Re: Learning SQL: nested CTE and UNION