Re: Cursors: getting the number of tuples; moving backwards

On Fri, Nov 01, 2002 at 12:43:48PM +0200, am@fx.ro wrote:
> Hello everyone!
> 
> I have 2 questions:
> 
> --1-- Some days ago, I've been trying to get the number of tuples
> that FETCH ALL would return,  *before* fetching anything.
> (the program is written in C++, using libpq ; PostgreSQL 7.2.3).

Well, to get an answer, the server needs to execute the entire query. It
won't do that unless you explicitly ask for it.

> The solution i've found was something like:
> 
>     int nr_tuples;
> 
>     res = PQexec(conn, "MOVE ALL in CURS");    
>     sscanf(PQcmdStatus(res),"MOVE %i",&nr_tuples);
>     PQclear(res);

That would work. But why do you need to know the total beforehand? You could
just do a FETCH ALL and then use PQntuples to get the number. If you're
using it to decide whether to provide a Next link, just FETCH one more item
than you intend to display and if you get it you display the link.

> I'm wondering: is there any better way to get that number?
> 
> ( just an idea:  maybe it would be useful to make PQcmdTuples
>   work for MOVE commands ... ? )

Interesting idea. I'm not sure whether MOVE actually executes the query or
not.

> --2-- I found out that if i reach the end of the cursor, and want
> to move backwards, i have to increase the MOVE command's argument by 1:

No idea, the cursor has probably moved off the end to indicate the query is
done. So you need the extra one to move it back. That's just a guess though.

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.