pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMOVE in SQL vs PLPGSQL
5+ messages / 4 participants
[nested] [flat]
* MOVE in SQL vs PLPGSQL
@ 2005-10-03 20:57 andrew@pillette.com
2005-10-05 08:18 ` Re: MOVE in SQL vs PLPGSQL Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: andrew@pillette.com @ 2005-10-03 20:57 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MOVE in SQL vs PLPGSQL
2005-10-03 20:57 MOVE in SQL vs PLPGSQL andrew@pillette.com
@ 2005-10-05 08:18 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-10-05 08:18 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MOVE in SQL vs PLPGSQL
@ 2005-10-05 15:01 Richard Huxton <dev@archonet.com>
2005-10-05 15:43 ` Re: MOVE in SQL vs PLPGSQL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2005-10-05 15:01 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MOVE in SQL vs PLPGSQL
2005-10-05 15:01 Re: MOVE in SQL vs PLPGSQL Richard Huxton <dev@archonet.com>
@ 2005-10-05 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-06 20:39 ` Re: MOVE in SQL vs PLPGSQL Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-10-05 15:43 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: andrew@pillette.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: MOVE in SQL vs PLPGSQL
2005-10-05 15:01 Re: MOVE in SQL vs PLPGSQL Richard Huxton <dev@archonet.com>
2005-10-05 15:43 ` Re: MOVE in SQL vs PLPGSQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-06 20:39 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Stark @ 2005-10-06 20:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; andrew@pillette.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-06 20:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-03 20:57 MOVE in SQL vs PLPGSQL andrew@pillette.com
2005-10-05 08:18 ` Richard Huxton <dev@archonet.com>
2005-10-05 15:01 Re: MOVE in SQL vs PLPGSQL Richard Huxton <dev@archonet.com>
2005-10-05 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-06 20:39 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox