Re: MOVE strangeness

On Thu, Dec 26, 2002 at 02:14:40PM -0500, Tom Lane wrote:                       
> Bruce Momjian <pgman@candle.pha.pa.us> writes:                                
> > Sorry, I am not understanding.  If he does:                                 
> > ...                                                                         
> > here, isn't he sitting at the start of the fourth row, no?                  
>                                                                               
> No.  He is sitting *on* the third row.  If he now does FETCH 1, he will       
> advance to and return the fourth row; on the other hand, if he does           
> FETCH -1, he will back up to and return the second row.
                                      
 
So we're talking about pre-increment and pre-decrement, and a cursor            
starting at position 0 in a 1-based array (or -1 in a 0-based one)?
                                                                                                                     
 
> BTW, looking at Date and the SQL spec, I now realize that the recently        
> made change to convert FETCH 0 into a no-op is wrong; per spec, FETCH         
> RELATIVE 0 means "re-fetch the current row, if any".  By analogy, MOVE 0      
> should probably return "MOVE 1" if you are on a real row, "MOVE 0" if         
> you are not, corresponding to the number of rows you'd have gotten from       
> FETCH 0.  Ugly, but ...
                                      
 
Okay, given that, is there really any reason why MOVE should return the         
number of rows that would have been fetched?  Why not report the number         
of rows moved?  Having two different MOVE commands from the same starting       
positions yield indistinguishable results yet bring you to different            
states is very, very awkward and possibly quite useless.  Better yet,           
why should FETCH report the number of rows fetched instead of the               
number of rows moved when you're going to extract the former from the           
PQresult's PQntuples() anyway?  The current status string is completely         
redundant in that case.
                                      
 
If the status string for MOVE is both inconclusive (as my case shows)           
and inconsistent (the MOVE 0 case) now, but it's linked to the status           
string for FETCH which is redundant, it seems logical to give them              
new, consistent semantics that are useful and intuitive.  Just report           
the number of rows _moved_ and we can all go back to lead rich,                 
meaningful lives.  I for one will also sleep better knowing that the            
number of rows reported is based on the same counting system as the             
number of rows requested in the SQL command that the status string is           
supposed to echo.  If FETCH 0 expects to fetch 1 row, why not have a            
result set of 1 row and a result string that echoes the command?
                                      
 
Which begs the question: is there anything special about the one-before-        
first row or can a cursor be moved to any arbitrary point outside its           
actual set of rows?  Oh, what odious web we weave / when first we               
practice to iteratewithoutthebenefitsofzero-basedarithmeticandhalf-             
openintervals.
                                                                                                                     
 
Jeroen