Обсуждение: Number of rows in a cursor ?
Hi ... Is it possible to get the total number of rows found in a cursor, or must I make a count(*) and then a select ? /BL
Bo Lorentsen wrote: > Is it possible to get the total number of rows found in a cursor, or > must I make a count(*) and then a select ? Perhaps: GET DIAGNOSTICS rc = ROW_COUNT; (see http://archives.postgresql.org/pgsql-novice/2003-06/msg00143.php and http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS) /Thomas.
You cannot count the number of rows in a cursor, unfortunately. I recently ran in to this problem. As far as I am aware, the only way to count them is to either iterate through them, or if you are only expecting one or two, perform multiple FETCHES and test if the record set returned is empty. http://archives.postgresql.org/pgsql-sql/2005-08/msg00208.php OPEN cur_overlap FOR EXECUTE 'SELECT *, ....'; FETCH cur_overlap INTO row_one; FETCH cur_overlap INTO row_two; IF (row_two.id IS NULL) THEN .... King regards, Neil. This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other useof the email by you is prohibited.
Thomas Borg Salling wrote: > Bo Lorentsen wrote: > >> Is it possible to get the total number of rows found in a cursor, or >> must I make a count(*) and then a select ? > > > Perhaps: > GET DIAGNOSTICS rc = ROW_COUNT; Ok, and when I use the C interface the "DECLARE .." function will return the row count ? Can I use PQntuples( res ) after the "DECLARE CURSOR" statement (if so, it don't work for me :-)) ? /BL
neil.saunders@accenture.com wrote: >You cannot count the number of rows in a cursor, unfortunately. I recently ran in to this problem. > > How sad, then I have to repeat the query, first for counting and last for data fetch :-( /BL
> neil.saunders@accenture.com wrote: > >>You cannot count the number of rows in a cursor, unfortunately. I recently >>ran in to this problem. >> > How sad, then I have to repeat the query, first for counting and last for > data fetch :-( > > /BL > If you need a count, why not just execute one of the methods to get a count. i.e.e select count(id) ...
On Aug 24, 2005, at 6:31 AM, Bo Lorentsen wrote: > How sad, then I have to repeat the query, first for counting and > last for data fetch :-( No, you can use the MOVE command and read how many rows you moved with something like MOVE LAST IN mycursor; http://www.postgresql.org/docs/8.0/interactive/sql-move.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Russell Simpkins wrote: > If you need a count, why not just execute one of the methods to get a > count. i.e.e select count(id) ... The reason why i don't just make a count and then a selection of data is performance. /BL
On Wed, Aug 24, 2005 at 02:50:07PM +0200, Bo Lorentsen wrote: > Russell Simpkins wrote: > >If you need a count, why not just execute one of the methods to get a > >count. i.e.e select count(id) ... > > The reason why i don't just make a count and then a selection of data > is performance. ...which is the same reason a cursor doesn't know how many rows it will fetch until you fetch them all (or MOVE to the end of the cursor, which fetches the rows internally). -- Michael Fuhr
Michael Fuhr wrote: >...which is the same reason a cursor doesn't know how many rows it will >fetch until you fetch them all (or MOVE to the end of the cursor, >which fetches the rows internally). > > So, Postgresql is not hidding something for me, it just, like me, don't know ? /BL
On Wed, Aug 24, 2005 at 04:29:00PM +0200, Bo Lorentsen wrote: > Michael Fuhr wrote: > >...which is the same reason a cursor doesn't know how many rows it will > >fetch until you fetch them all (or MOVE to the end of the cursor, > >which fetches the rows internally). > > So, Postgresql is not hidding something for me, it just, like me, don't > know ? Right -- when you open a cursor PostgreSQL doesn't know how many rows it will return. PostgreSQL selects a query plan based on an *estimate* of how many rows the query will return, but until you fetch all the rows you can't know for sure how many rows there will be. -- Michael Fuhr
Michael Fuhr wrote: >Right -- when you open a cursor PostgreSQL doesn't know how many >rows it will return. PostgreSQL selects a query plan based on an >*estimate* of how many rows the query will return, but until you >fetch all the rows you can't know for sure how many rows there will >be. > > So if i make a but data set as result of a cursor I only "pay" for the rows I actually fetch ? /BL
On Thu, Aug 25, 2005 at 08:51:59 +0200, Bo Lorentsen <bl@netgroup.dk> wrote: > Michael Fuhr wrote: > > >Right -- when you open a cursor PostgreSQL doesn't know how many > >rows it will return. PostgreSQL selects a query plan based on an > >*estimate* of how many rows the query will return, but until you > >fetch all the rows you can't know for sure how many rows there will > >be. > > > > > So if i make a but data set as result of a cursor I only "pay" for the > rows I actually fetch ? There is usually some overhead for set up, though I believe quick starting plans are favored for cursor queries.