Thread: MOVE in SQL vs PLPGSQL

I can't find a good way to skip over a large number of records in PLPGSQL (I want to fast-forward and I don't need the
I/Oof reading and throwing away hundreds of records.) In SQL, I could just use MOVE. That doesn't appear to be
supportedin PLPGSQL?! Help?
 

andrew@pillette.com wrote:
> I can't find a good way to skip over a large number of records in
> PLPGSQL (I want to fast-forward and I don't need the I/O of reading
> and throwing away hundreds of records.) In SQL, I could just use
> MOVE. That doesn't appear to be supported in PLPGSQL?! Help?

Andrew - your question doesn't seem to make sense - can you give some 
more details? Are you having some problem with cursors in plpgsql?

--  Richard Huxton  Archonet Ltd

andrew@pillette.com wrote:
>> Andrew - your question doesn't seem to make sense - can you give
>> some more details? Are you having some problem with cursors in
>> plpgsql?
> 
> 
> Yes. I'd like the effect of the plain SQL MOVE with a PLPGSQL cursor.
> I'm writing a procedure for which SQL is inadequate (I need
> condiitonal branches and looping). However, in PLPGSQL, it appears
> the only way to fast forward on a cursor is a repeated FETCH, which
> is wasteful. PLPGSQL doesn't even allow me to set up a SQL-type
> cursor using EXECUTE--I have to use the refcursor datatype and it
> doesn't appear to support MOVE.
> 
> I could write a procedure client-side in (say) Java without any
> problem using absolute or relative move, but I'd like the procedure
> to run on the server.

Ah, now I see what you're saying. You're quite right in your suspicions, 
"MOVE..." isn't supported for plpgsql cursors. You could probably do 
something with EXECUTE and returning a refcursor from a previous 
function, but that sounds fiddly.

I must admit, on the odd occasion I want to skip a row, I just FETCH it 
and move on. Anyone else?

--   Richard Huxton  Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Ah, now I see what you're saying. You're quite right in your suspicions, 
> "MOVE..." isn't supported for plpgsql cursors. You could probably do 
> something with EXECUTE and returning a refcursor from a previous 
> function, but that sounds fiddly.

> I must admit, on the odd occasion I want to skip a row, I just FETCH it 
> and move on. Anyone else?

There is something on the TODO list about improving plpgsql's cursor
functionality --- there's no reason it shouldn't have MOVE, except that
no one got around to it yet.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Richard Huxton <dev@archonet.com> writes:
> > Ah, now I see what you're saying. You're quite right in your suspicions, 
> > "MOVE..." isn't supported for plpgsql cursors. You could probably do 
> > something with EXECUTE and returning a refcursor from a previous 
> > function, but that sounds fiddly.
> 
> > I must admit, on the odd occasion I want to skip a row, I just FETCH it 
> > and move on. Anyone else?
> 
> There is something on the TODO list about improving plpgsql's cursor
> functionality --- there's no reason it shouldn't have MOVE, except that
> no one got around to it yet.

Though the original poster should realize, a MOVE command would be only
marginally more efficient than just fetching those records. It would save the
network overhead and context switches involved in communicating those records,
but there's no way it would let the server avoid reading all those records
from disk.

At least as far as I can see.

-- 
greg