Date: Thu, 11 Jan 2007 19:41:45 -0500
From: L Bayuk <lbayuk@pobox.com>
To: Jan van der Weijde <Jan.van.der.Weijde@attachmate.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: How do I detect End-of-table or End-of-limit ?
Message-ID: <20070112004145.GA356@mail.mindspring.com>
References: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4B9C73D1EB78FE4A81475AE8A553B3C67DC52B@exch-lei1.attachmate.com>
User-Agent: Mutt/1.4.2.2i

On Thu, Jan 11, 2007 at 11:30:48AM +0100, Jan van der Weijde wrote:
> Hello all,
>  
> I am using the libpq interface method PQexec() to execute SELECT
> statements with a LIMIT clause. I declare a cursor with hold and use
> PQecec() to execute FETCH NEXT on that cursor.
> At a certain moment PQntuples() returns 0 after a fetch, but I cannot
> check for the actual reason: is the physical end of the table (or
> selection) reached or is the end of my limit reached.
> In the former case I'm done. But in the latter case I can continue
> selecting records by using SELECT with a LIMIT and OFFSET clause. 
>  ...

I'm not sure if this applies to you, but what I do is if I want N records,
I select with limit N+1. If I get N or fewer records back, it reached end
of table.  If I get N+1 back, I process the first N of them and discard the
last, and I know there are more records. Next query starts at the discarded
one.  So there is 1 record overlap on the queries. And unless the data
changes between queries, you will never get 0 records back.