Re: How to get the total number of rows returned by query - Mailing list pgsql-general

From Egor Shipovalov
Subject Re: How to get the total number of rows returned by query
Date
Msg-id AKEFIKCGEAEFBICLBFBMCEJDCIAA.pgsql_list@eonline.ru
Whole thread Raw
In response to Re: How to get the total number of rows returned by query  (Shridhar Daithankar <shridhar_daithankar@persistent.co.in>)
List pgsql-general
Thanks. In fact, I've thought about that too, but it doesn't seem to work.
I'm using Perl Pg module which, I'm guessing, is just a wrapper around
libpq. This is the code I used to test it:

---------------
use Pg;

$conn = Pg::connectdb('user=my_user dbname=my_db');
$conn->exec('BEGIN');
$r = $conn->exec("DECLARE my_cursor CURSOR FOR SELECT nick FROM users ORDER
BY nick DESC");

print $r->ntuples; # prints 0

$r = $conn->exec('FETCH 10 FROM my_cursor');

print $r->ntuples; # prints 10
--------------

Too bad we seem to be stuck with COUNT(*)  for now. Are there any
fundamental problems getting to that cursor count data?
Judging from the lack of absolute cursor positioning it appears somewhat
like so ;(

Best regards,
Egor Shipovalov.


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Shridhar
> Daithankar
> Sent: Tuesday, September 23, 2003 18:59
> To: Pgsql-General
> Subject: Re: [GENERAL] How to get the total number of rows
> returned by query
>
>
> Egor Shipovalov wrote:
>
> > I'm implementing paging through search results using cursors. Is there a
> > better way to know total number of rows under a cursor than running a
> > separate COUNT(*) query? I think PostgreSQL is bound to know this number
> > after the first FETCH, isn't it?
>
> Using libpq, yes. You can use PQntuples. See
> http://developer.postgresql.org/docs/postgres/libpq-exec.html#LIBP
> Q-EXEC-SELECT-INFO
>
> Unfortunately this will retrieve the entire resultselt before
> returning the
> control. When libpq will have chuncking of data, this will be much more
> flexible, as you are expecting it now, I guess.
>
>   Shridhar
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


pgsql-general by date:

Previous
From: "scott.marlowe"
Date:
Subject: Re: Scalability (both vertical and horizontal)?
Next
From: Tom Lane
Date:
Subject: Re: How to get the total number of rows returned by query when using a cursor?