SQL92 defines the cursor-fetch command as
<fetch statement> ::= FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO
<fetchtarget list>
<fetch orientation> ::= NEXT | PRIOR | FIRST | LAST
| { ABSOLUTE | RELATIVE } <simple value specification>
<fetch target list> ::= <target specification> [ { <comma> <target specification> }... ]
(Ignore the INTO bit, which is only relevant for embedded SQL.)
AFAICT all of these are equivalent to a MOVE of some amount followed by
FETCH 1. In particular, "FETCH RELATIVE n" means to move n rows and
return the last of these rows; it does not mean to return all n rows,
as Postgres currently misinterprets it to do.
Does anyone object to making the above-mentioned syntaxes do what the
spec says they should do? We would also keep the following non-spec
syntaxes:
FETCH n -- retrieve next n rowsFETCH ALL -- retrieve all remaining rowsFETCH FORWARD --
equivalentto FETCH NEXTFETCH FORWARD n/ALL -- FORWARD is noise hereFETCH BACKWARD -- equivalent to FETCH
PRIORFETCHBACKWARD n/ALL -- retrieve n or all previous rows
As before, negative n reverses the forward/backward semantics, and
zero n re-fetches the current row (like FETCH RELATIVE 0 does per-spec).
I would like to remove the following currently-allowed-but-nonstandard
syntaxes:
FETCH RELATIVE -- n must be given, per specFETCH FORWARD NEXT -- redundantFETCH FORWARD PRIOR --
contradictionin termsFETCH BACKWARD NEXT -- contradiction in termsFETCH BACKWARD PRIOR -- redundantFETCH RELATIVE
ALL -- not standard, may as well use FORWARDFETCH RELATIVE NEXT -- dittoFETCH RELATIVE PRIOR -- ditto
MOVE would get the same syntax changes. It would still be defined to
reposition the cursor exactly as FETCH would do with the same
parameters, but not return any actual row data.
I am not sure what the command status string should be for MOVE with the
SQL-specified syntaxes. For strict compatibility with our historical
behavior (return the number of rows moved over) it should always be
"MOVE 1" (successful move) or "MOVE 0" (ran off end). But I'm not sure
that's especially useful. Would it be better to return the final
position (row number)? If so, should we rethink what MOVE returns for
the Postgres cases?
regards, tom lane