BUG #15200: Support ANSI OFFSET .. FETCH syntax with bind variables

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: lukas(dot)eder(at)gmail(dot)com
Subject: BUG #15200: Support ANSI OFFSET .. FETCH syntax with bind variables
Date: 2018-05-16 13:36:43
Message-ID: 152647780335.27204.16895288237122418685@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15200
Logged by: Lukas Eder
Email address: lukas(dot)eder(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2018-05-16 14:00:02 Re: BUG #15198: nextval() accepts tables/indexes when adding a default to a column
Previous Message John Bester 2018-05-16 13:02:09 JDBC problem in 10.3 / 10.4