syntax error WITH ORDINALITY

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs