Thread: 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