Thread: Parameters don't work in FETCH NEXT clause?

Parameters don't work in FETCH NEXT clause?

From
Shay Rojansky
Date:
A user of mine just raised a strange issue... While it is possible to use a parameter in a LIMIT clause, PostgreSQL does not seem to allow using one in a FETCH NEXT clause. In other words, while the following works:

SELECT 1 LIMIT $1;

The following generates a syntax error:

SELECT 1 FETCH NEXT $1 ROWS ONLY;

Since LIMIT and FETCH NEXT are supposed to be equivalent this behavior is odd.

More generally, is there some documentation on where exactly PostgreSQL allows parameters and where it doesn't? I occasionally get complaints from users expecting parameters to work in DDL, or even in table/column names in SELECT queries... I haven't seen a resource like this.

Re: Parameters don't work in FETCH NEXT clause?

From
Tom Lane
Date:
Shay Rojansky <roji@roji.org> writes:
> A user of mine just raised a strange issue... While it is possible to use a
> parameter in a LIMIT clause, PostgreSQL does not seem to allow using one in
> a FETCH NEXT clause. In other words, while the following works:
> SELECT 1 LIMIT $1;
> The following generates a syntax error:
> SELECT 1 FETCH NEXT $1 ROWS ONLY;
> Since LIMIT and FETCH NEXT are supposed to be equivalent this behavior is
> odd.

Per the SELECT reference page:
   SQL:2008 introduced a different syntax to achieve the same result,   which PostgreSQL also supports. It is:
       OFFSET start { ROW | ROWS }       FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
   In this syntax, to write anything except a simple integer constant for   start or count, you must write parentheses
aroundit.
 

The comments about this in gram.y are informative:
* Allowing full expressions without parentheses causes various parsing* problems with the trailing ROW/ROWS key words.
SQLonly calls for* constants, so we allow the rest only with parentheses.  If omitted,* default to 1.
 
        regards, tom lane



Re: Parameters don't work in FETCH NEXT clause?

From
Shay Rojansky
Date:
<div dir="ltr">Apologies, as usual I didn't read the docs carefully enough.</div><div class="gmail_extra"><br /><div
class="gmail_quote">OnTue, May 17, 2016 at 7:13 PM, Tom Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us"
target="_blank">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex">Shay Rojansky <<a href="mailto:roji@roji.org">roji@roji.org</a>>
writes:<br/> > A user of mine just raised a strange issue... While it is possible to use a<br /> > parameter in a
LIMITclause, PostgreSQL does not seem to allow using one in<br /> > a FETCH NEXT clause. In other words, while the
followingworks:<br /> > SELECT 1 LIMIT $1;<br /> > The following generates a syntax error:<br /> > SELECT 1
FETCHNEXT $1 ROWS ONLY;<br /> > Since LIMIT and FETCH NEXT are supposed to be equivalent this behavior is<br /> >
odd.<br/><br /> Per the SELECT reference page:<br /><br />     SQL:2008 introduced a different syntax to achieve the
sameresult,<br />     which PostgreSQL also supports. It is:<br /><br />         OFFSET start { ROW | ROWS }<br />    
   FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY<br /><br />     In this syntax, to write anything except a
simpleinteger constant for<br />     start or count, you must write parentheses around it.<br /><br /> The comments
aboutthis in gram.y are informative:<br /><br />  * Allowing full expressions without parentheses causes various
parsing<br/>  * problems with the trailing ROW/ROWS key words.  SQL only calls for<br />  * constants, so we allow the
restonly with parentheses.  If omitted,<br />  * default to 1.<br /><br />                         regards, tom lane<br
/></blockquote></div><br/></div> 

Re: Parameters don't work in FETCH NEXT clause?

From
"David G. Johnston"
Date:
On Tue, May 17, 2016 at 12:15 PM, Shay Rojansky <roji@roji.org> wrote:
Apologies, as usual I didn't read the docs carefully enough.

On Tue, May 17, 2016 at 7:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Shay Rojansky <roji@roji.org> writes:
> A user of mine just raised a strange issue... While it is possible to use a
> parameter in a LIMIT clause, PostgreSQL does not seem to allow using one in
> a FETCH NEXT clause. In other words, while the following works:
> SELECT 1 LIMIT $1;
> The following generates a syntax error:
> SELECT 1 FETCH NEXT $1 ROWS ONLY;
> Since LIMIT and FETCH NEXT are supposed to be equivalent this behavior is
> odd.

Per the SELECT reference page:

    SQL:2008 introduced a different syntax to achieve the same result,
    which PostgreSQL also supports. It is:

        OFFSET start { ROW | ROWS }
        FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY

    In this syntax, to write anything except a simple integer constant for
    start or count, you must write parentheses around it.

The comments about this in gram.y are informative:

 * Allowing full expressions without parentheses causes various parsing
 * problems with the trailing ROW/ROWS key words.  SQL only calls for
 * constants, so we allow the rest only with parentheses.  If omitted,
 * default to 1.

​Would something like this be valid?

OFFSET { start_literal | ( start_expression ) } { ROW | ROWS }
FETCH { FIRST | NEXT} [ count_literal | ( count_expression ) ] { ROW | ROWS } ONLY

Leaving the mandatory parentheses detail to the description, while adequate, seems insufficient - especially when a normal LIMIT expression is not so restricted.

​And don't you think the section header would be more accurately named:

Limit, Offset & Fetch Clauses

​The nuance regarding "different standard syntax" is unknown to the reader who first looks at the syntax and sees three different lines, one for each clause, and then scrolls down looking at headers until they find the section for the clause they are interested in.  That FETCH is an alias for LIMIT ​is not something that I immediately understood - though to be honest I don't think I comprehended the presence of FETCH on a SELECT query at all and thought it only pertained to cursors....

David J.


Re: Parameters don't work in FETCH NEXT clause?

From
Shay Rojansky
Date:
​Would something like this be valid?

OFFSET { start_literal | ( start_expression ) } { ROW | ROWS }
FETCH { FIRST | NEXT} [ count_literal | ( count_expression ) ] { ROW | ROWS } ONLY

Leaving the mandatory parentheses detail to the description, while adequate, seems insufficient - especially when a normal LIMIT expression is not so restricted.

​And don't you think the section header would be more accurately named:

Limit, Offset & Fetch Clauses

​The nuance regarding "different standard syntax" is unknown to the reader who first looks at the syntax and sees three different lines, one for each clause, and then scrolls down looking at headers until they find the section for the clause they are interested in.  That FETCH is an alias for LIMIT ​is not something that I immediately understood - though to be honest I don't think I comprehended the presence of FETCH on a SELECT query at all and thought it only pertained to cursors....


All these suggestions would definitely have saved me (and therefore this list!) some time.