SQL92 defines the cursor-fetch command as
<fetch statement> ::=
FETCH [ [ <fetch orientation> ] FROM ] <cursor name>
INTO <fetch target 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 rows
FETCH ALL -- retrieve all remaining rows
FETCH FORWARD -- equivalent to FETCH NEXT
FETCH FORWARD n/ALL -- FORWARD is noise here
FETCH BACKWARD -- equivalent to FETCH PRIOR
FETCH BACKWARD 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 spec
FETCH FORWARD NEXT -- redundant
FETCH FORWARD PRIOR -- contradiction in terms
FETCH BACKWARD NEXT -- contradiction in terms
FETCH BACKWARD PRIOR -- redundant
FETCH RELATIVE ALL -- not standard, may as well use FORWARD
FETCH RELATIVE NEXT -- ditto
FETCH 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
