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
Re: Learning SQL: nested CTE and UNION |
Список | 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 по дате отправления: