From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | SQL:2008 LIMIT/OFFSET |
Date: | 2008-10-20 09:16:32 |
Message-ID: | 48FC4C70.8030407@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
SQL:2008 specifies the following syntax for what we have so far called
LIMIT and OFFSET
SELECT ... [ ORDER BY ... ]
OFFSET num {ROW|ROWS} FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY
For example,
SELECT id, name FROM tab1 ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS
ONLY;
(I understand this syntax was taken from IBM.)
Supporting this in PostgreSQL poses a couple of parsing challenges that
involve some tradeoffs. I have attached a draft patch if you want to
follow along.
FETCH must become reserved. It's in the same position now that LIMIT
and OFFSET are already. This should be OK because FETCH is already a
well-known SQL command for cursor use.
The trailing {ROW|ROWS} key words plus the fact that the number
specification is optional after FETCH (defaulting to 1) cause some
independent problems because ROWS is unreserved and ROW can introduce an
expression (c_expr even).
If we want to avoid reshuffling the expression syntax (always good to
avoid) and avoid making ROWS reserved, we need to make some arbitrary
restrictions on what kinds of expressions can be used in these clauses.
Considering that specifying arbitrary expressions in these places
isn't terribly common and the SQL standard only calls for literals, I
hope I have found a good balance that satisfies the letter of the
standard and works well in practice with some parentheses needed in
complicated cases. But it may be objected to because it creates some
inconsistencies between the traditional and the new syntax in more
complex cases.
Another question, if we want to go this route, is whether we would want
to change the query tree reversing to use the new syntax.
Comments?
Attachment | Content-Type | Size |
---|---|---|
sql2008-offset-limit.diff | text/plain | 2.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Wanner | 2008-10-20 09:23:07 | Re: Block-level CRC checks |
Previous Message | Magnus Hagander | 2008-10-20 09:02:58 | crypt auth |