Re: MOVE strangeness

Tom Lane wrote:
> > Okay. But then doesn't it make sense for FETCH to fetch the contents
> > of the row (and subsequent requested rows) that the cursor is
> > currently on *then* move, and not the other way around?
>
> No, because WHERE CURRENT OF operates on the row last returned by FETCH,
> according to the spec. AFAICT, the conceptual model I described a
> couple messages back corresponds directly to a subset of the SQL92
> specification for cursors: "before first row", "on some row", and "after
> last row" are all states described by the spec. (It is a subset because
> we don't support DELETE WHERE CURRENT OF --- if we did, we'd need
> additional states to handle the situation where the cursor is pointing
> at a just-deleted row.)
>
> We don't get to define our own behavior for FETCH.

I know. I just think it's unfortunate that the spec was written the
way it was, because the behavior the spec calls for is not what I
think most people would expect of a cursor.

When I think of a cursor's behavior, what I would normally expect is
for it to behave the way my editor's cursor behaves. The cursor is
either on a character or on the position after the last character.
When I type in insert mode, the characters I type are inserted before
the character the cursor is on. When I type in overstrike ("update")
mode, the character the cursor is on is replaced by the character I
typed and the cursor is moved forward one position. When I delete the
character the cursor is on (using the DEL key), the character the
cursor is on is deleted and the character that followed now becomes
the character the cursor sits on. When I backspace, the character
before the cursor is deleted and the cursor remains on the same
character it was on prior to the backspace operation.

Some editors use a very thin cursor that is always placed between
characters, but the resulting semantics are basically the same as
described above.

This model is extremely common and, IMO, sensible. If SQL cursors
were implemented with the same semantics, then UPDATE WHERE CURRENT OF
would update the row the cursor currently sits on, then (perhaps) move
the cursor forward, just as you'd expect (since it's the equivalent of
typing a character in overstrike mode). INSERT WHERE CURRENT OF would
insert a new row before the row the cursor is on (so to insert a row
at the end you'd have to move the cursor past the last row) and the
cursor would remain positioned on the row it was on. DELETE WHERE
CURRENT OF would, of course, delete the number of rows requested,
starting with the row the cursor is on, and position the first
remaining row after the deleted set underneath the cursor. There
would be no "cursor points to the deleted row" because such a thing
makes no sense and doesn't have any real use that I'm aware of
(correct me if I'm wrong, please).

There's no need to have a "before the first row" position *and* an
"after the last row" position, since INSERT must always insert either
before the current row or after the current row (before if you use the
same semantics I described above).

None of this matters, of course, because the SQL spec calls for a
completely different behavior.

My question is: *why* does it call for such a completely different
behavior? Does anyone here have any insight into that?

--
Kevin Brown kevin(at)sysexperts(dot)com