Обсуждение: Number of items in a cursor...
Is there any way to get the numbers of items inside a cursor?
Cristian Prieto wrote: > Is there any way to get the numbers of items inside a cursor? I can't see a way to do it except to do a FETCH ALL and count the returned rows. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Eric B. Ridge wrote: > On Nov 15, 2005, at 12:43 PM, Bruce Momjian wrote: > > > Cristian Prieto wrote: > >> Is there any way to get the numbers of items inside a cursor? > > > > I can't see a way to do it except to do a FETCH ALL and count the > > returned rows. > > What we do, via JDBC is: > > MOVE <Integer.MAX_VALUE> IN cursor_name; > > The JDBC drivers are nice enough to return the output message from > the MOVE command, which is the number of records moved. We just keep > doing this until it returns something less than <Integer.MAX_VALUE>. > The sum of all the moves is the total number of records. Then we > just "MOVE ABSOLUTE 0 in cursor_name;" to make use of the cursor > using FETCH. > > While this does force the server to process the entire query it at > least avoids the overhead of returning all the records (which is the > point of cursors!). Yep, that works: test=> BEGIN; BEGIN test=> DECLARE xx CURSOR FOR SELECT * FROM pg_language; DECLARE CURSOR test=> MOVE 9999999 from xx; MOVE 3 Notice the "MOVE 3" returned. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Nov 15, 2005, at 12:43 PM, Bruce Momjian wrote: > Cristian Prieto wrote: >> Is there any way to get the numbers of items inside a cursor? > > I can't see a way to do it except to do a FETCH ALL and count the > returned rows. What we do, via JDBC is: MOVE <Integer.MAX_VALUE> IN cursor_name; The JDBC drivers are nice enough to return the output message from the MOVE command, which is the number of records moved. We just keep doing this until it returns something less than <Integer.MAX_VALUE>. The sum of all the moves is the total number of records. Then we just "MOVE ABSOLUTE 0 in cursor_name;" to make use of the cursor using FETCH. While this does force the server to process the entire query it at least avoids the overhead of returning all the records (which is the point of cursors!). eric