BUG #15200: Support ANSI OFFSET .. FETCH syntax with bind variables
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: lukas.eder@gmail.com
Date: 2018-05-16T13:36:43Z
Lists: pgsql-bugs
The following bug has been logged on the website:
Bug reference: 15200
Logged by: Lukas Eder
Email address: lukas.eder@gmail.com
PostgreSQL version: 10.4
Operating system: Windows
Description:
The manual states [1]:
> 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.
And as shown in this Stack Overflow question [2], it can be shown that the
standard syntax doesn't work with anything but constant literals, including
bind variables (which to me, are a kind of constant literal). This is
regrettable, the workaround when using this syntax from Java is to write:
OFFSET (?) ROWS FETCH FIRST (?) ROWS ONLY
Instead of (as in other databases):
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
This is also inconsistent with OFFSET .. LIMIT. The following works just
fine:
OFFSET ? LIMIT ?
I suggest relaxing this syntactic limitation and allowing for at least
constant literals AND bind variables in this syntax
[1] https://www.postgresql.org/docs/10/static/sql-select.html#SQL-LIMIT
[2] https://stackoverflow.com/q/50371757/521799
Commits
-
Fix SQL:2008 FETCH FIRST syntax to allow parameters.
- 89b09db01b43 9.3.24 landed
- 769e6fcd1a35 9.4.19 landed
- 3b0fb2529fdf 9.5.14 landed
- 7a0aa8d12ace 9.6.10 landed
- cf516dc9d690 10.5 landed
- 1da162e1f5a7 11.0 landed