pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNumber of rows in a cursor ?
13+ messages / 7 participants
[nested] [flat]
* Number of rows in a cursor ?
@ 2005-08-24 09:22 Bo Lorentsen <bl@netgroup.dk>
2005-08-24 09:55 ` Re: Number of rows in a cursor ? Thomas Borg Salling <tbs@navicon.dk>
0 siblings, 1 reply; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-24 09:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 09:22 Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-24 09:55 ` Thomas Borg Salling <tbs@navicon.dk>
2005-08-24 10:27 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Borg Salling @ 2005-08-24 09:55 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: pgsql-sql
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-DIAGNOSTIC...)
/Thomas.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 09:22 Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 09:55 ` Re: Number of rows in a cursor ? Thomas Borg Salling <tbs@navicon.dk>
@ 2005-08-24 10:27 ` Bo Lorentsen <bl@netgroup.dk>
0 siblings, 0 replies; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-24 10:27 UTC (permalink / raw)
To: Thomas Borg Salling <tbs@navicon.dk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
@ 2005-08-24 10:00 neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
0 siblings, 1 reply; 13+ messages in thread
From: neil.saunders@accenture.com @ 2005-08-24 10:00 UTC (permalink / raw)
To: pgsql-sql
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 use of the email by you is prohibited.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
@ 2005-08-24 10:31 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:08 ` Re: Number of rows in a cursor ? John DeSoi <desoi@pgedit.com>
0 siblings, 2 replies; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-24 10:31 UTC (permalink / raw)
To: neil.saunders@accenture.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-24 11:36 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
1 sibling, 1 reply; 13+ messages in thread
From: Russell Simpkins @ 2005-08-24 11:36 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: pgsql-sql
> 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) ...
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
@ 2005-08-24 12:50 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-24 12:50 UTC (permalink / raw)
To: Russell Simpkins <russellsimpkins@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-24 13:19 ` Michael Fuhr <mike@fuhr.org>
2005-08-24 14:29 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Fuhr @ 2005-08-24 13:19 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
@ 2005-08-24 14:29 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 17:46 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-24 14:29 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
2005-08-24 14:29 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-24 17:46 ` Michael Fuhr <mike@fuhr.org>
2005-08-25 06:51 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Fuhr @ 2005-08-24 17:46 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
2005-08-24 14:29 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 17:46 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
@ 2005-08-25 06:51 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-25 13:10 ` Re: Number of rows in a cursor ? Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 13+ messages in thread
From: Bo Lorentsen @ 2005-08-25 06:51 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Re: Number of rows in a cursor ? Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
2005-08-24 14:29 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 17:46 ` Re: Number of rows in a cursor ? Michael Fuhr <mike@fuhr.org>
2005-08-25 06:51 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-25 13:10 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 13+ messages in thread
From: Bruno Wolff III @ 2005-08-25 13:10 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: Michael Fuhr <mike@fuhr.org>; Russell Simpkins <russellsimpkins@hotmail.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Number of rows in a cursor ?
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Re: Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
@ 2005-08-24 12:08 ` John DeSoi <desoi@pgedit.com>
1 sibling, 0 replies; 13+ messages in thread
From: John DeSoi @ 2005-08-24 12:08 UTC (permalink / raw)
To: Bo Lorentsen <bl@netgroup.dk>; +Cc: neil.saunders@accenture.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-08-25 13:10 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-24 09:22 Number of rows in a cursor ? Bo Lorentsen <bl@netgroup.dk>
2005-08-24 09:55 ` Thomas Borg Salling <tbs@navicon.dk>
2005-08-24 10:27 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 10:00 Re: Number of rows in a cursor ? neil.saunders@accenture.com
2005-08-24 10:31 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 11:36 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-08-24 12:50 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 13:19 ` Michael Fuhr <mike@fuhr.org>
2005-08-24 14:29 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-24 17:46 ` Michael Fuhr <mike@fuhr.org>
2005-08-25 06:51 ` Bo Lorentsen <bl@netgroup.dk>
2005-08-25 13:10 ` Bruno Wolff III <bruno@wolff.to>
2005-08-24 12:08 ` John DeSoi <desoi@pgedit.com>
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