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

Mon, 10 Mar 2003 16:07:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Making FETCH more spec-compliant