BUG #15462: WITH ORDINALITY and CTEs don't work

Поиск
Список
Период
Сортировка
Искать

BUG #15462: WITH ORDINALITY and CTEs don't work

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      15462
Logged by:          Michael Meyer
Email address:      michael.meyer@pace.de
PostgreSQL version: 11.0
Operating system:   Windows 10
Description:        

CTEs don't play nicely together with 'WITH ORDINALITY':

This is working:

SELECT * FROM generate_series(4,1,-1) WITH ORDINALITY;
WITH a AS (SELECT * FROM generate_series(4,1,-1) )
SELECT * FROM a;

whereas this yields syntax error (42601)

WITH a AS (SELECT * FROM generate_series(4,1,-1) )
SELECT * FROM a WITH ORDINALITY;

Re: BUG #15462: WITH ORDINALITY and CTEs don't work

От:
hubert depesz lubaczewski <depesz@depesz.com>
Дата:
On Fri, Oct 26, 2018 at 10:55:57AM +0000, PG Bug reporting form wrote:
> CTEs don't play nicely together with 'WITH ORDINALITY':

Sure they do.

> This is working:
> SELECT * FROM generate_series(4,1,-1) WITH ORDINALITY;
> WITH a AS (SELECT * FROM generate_series(4,1,-1) )
> SELECT * FROM a;
> 
> whereas this yields syntax error (42601)
> 
> WITH a AS (SELECT * FROM generate_series(4,1,-1) )
> SELECT * FROM a WITH ORDINALITY;


This is because "with ORDINALITY" is for function call, not for any
table query.

So the correct syntax would be:

WITH a AS (SELECT * FROM generate_series(4,1,-1) WITH ORDINALITY) SELECT * FROM a;

depesz
FAQ