Re: How to get the total number of rows returned by query

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