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

On Fri, Nov 01, 2002 at 12:43:48PM +0200, am(at)fx(dot)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(at)svana(dot)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.