Skip site navigation (1) Skip section navigation (2)

Re: Learning SQL: nested CTE and UNION

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Mackler <adammackler(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Learning SQL: nested CTE and UNION
Date: 2012-07-31 17:43:31
Message-ID: 13313.1343756611@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice
Adam Mackler <adammackler(at)gmail(dot)com> writes:
> ... 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

This is a bug :-(.  The parse analysis code seems to think that WITH can
only be attached to the top level or a leaf-level SELECT within a set
operation tree; but the grammar follows the SQL standard which says
no such thing.  The WITH gets accepted, and attached to the
intermediate-level UNION which is where syntactically it should go,
and then it's entirely ignored during parse analysis.  Will see about
fixing it.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: George McIlwaineDate: 2012-07-31 21:16:59
Subject: postgres domain service account won't start the postgres service
Previous:From: Adam MacklerDate: 2012-07-31 14:47:20
Subject: Re: Learning SQL: nested CTE and UNION

pgsql-hackers by date

Next:From: Palle GirgensohnDate: 2012-07-31 19:02:14
Subject: build postgresql on Mac OS X mountain lion with ossp-uuid
Previous:From: Fujii MasaoDate: 2012-07-31 16:50:00
Subject: Re: several problems in pg_receivexlog

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group