Обсуждение: 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