Обсуждение: syntax error WITH ORDINALITY

postgres=# select * from unnest(ARRAY[1,2,3]) with ordinality;

 unnest | ordinality
--------+------------
      1 |          1
      2 |          2
      3 |          3

postgres=# select unnest(ARRAY[1,2,3]) with ordinality;

ERROR:  syntax error at or near "ordinality"
LINE 1: select unnest(ARRAY[1,2,3]) with ordinality;

though this works

postgres=# select unnest(ARRAY[1,2,3]);

 unnest
--------
      1
      2
      3

The manual
http://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
says
"Columns returned by table functions can be included in SELECT, JOIN,
or WHERE clauses in the same manner as columns of a table, view, or
subquery."
There is no qualification of that for WITH ORDINALITY, nor an example

So either the manual is wrong, or the syntax is.

Also, WITH ORDINALITY is not mentioned here...
http://www.postgresql.org/docs/devel/static/functions-array.html

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon@2ndQuadrant.com> writes:
> postgres=# select * from unnest(ARRAY[1,2,3]) with ordinality;
>  unnest | ordinality
> --------+------------
>       1 |          1
>       2 |          2
>       3 |          3

> postgres=# select unnest(ARRAY[1,2,3]) with ordinality;

> ERROR:  syntax error at or near "ordinality"
> LINE 1: select unnest(ARRAY[1,2,3]) with ordinality;

This is not a bug.  The WITH ORDINALITY option is only available in FROM.
I don't see any place in the documentation that suggests otherwise.

> Also, WITH ORDINALITY is not mentioned here...
> http://www.postgresql.org/docs/devel/static/functions-array.html

Indeed.

            regards, tom lane

Simon Riggs wrote
> postgres=# select * from unnest(ARRAY[1,2,3]) with ordinality;
>
>  unnest | ordinality
> --------+------------
>       1 |          1
>       2 |          2
>       3 |          3
>
> postgres=# select unnest(ARRAY[1,2,3]) with ordinality;
>
> ERROR:  syntax error at or near "ordinality"
> LINE 1: select unnest(ARRAY[1,2,3]) with ordinality;
>
> though this works
>
> postgres=# select unnest(ARRAY[1,2,3]);
>
>  unnest
> --------
>       1
>       2
>       3
>
> The manual
> http://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
> says
> "Columns returned by table functions can be included in SELECT, JOIN,
> or WHERE clauses in the same manner as columns of a table, view, or
> subquery."
> There is no qualification of that for WITH ORDINALITY, nor an example

A function used in the select-list of a query does not constitute a "table
expression" and so is not covered by this particular section of the
documentation.

From your link:

"Table functions are functions that produce a set of rows [...]. They are
used like a table, view, or subquery in the FROM clause of a query."

So even if the documentation could be improved altering this section to deal
with select-list invocations of set-returning functions seems like an
overkill and verbose solution.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/syntax-error-WITH-ORDINALITY-tp5812550p5812568.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On 23 July 2014 18:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> postgres=# select * from unnest(ARRAY[1,2,3]) with ordinality;
>>  unnest | ordinality
>> --------+------------
>>       1 |          1
>>       2 |          2
>>       3 |          3
>
>> postgres=# select unnest(ARRAY[1,2,3]) with ordinality;
>
>> ERROR:  syntax error at or near "ordinality"
>> LINE 1: select unnest(ARRAY[1,2,3]) with ordinality;
>
> This is not a bug.  The WITH ORDINALITY option is only available in FROM.
> I don't see any place in the documentation that suggests otherwise.

The phrase "The WITH ORDINALITY option is only available in FROM." is
a clear and useful statement; it isn't in the docs and should be.

Or we should have an ERROR message that says "WITH ORDINALITY may not
be used in the SELECT clause".

Perhaps both.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services