[bind10-dev] missing C API for PostgreSQL: row-by-row query result retrieval

[Mukund Sivaraman]

Hi Shane

On Wed, Oct 03, 2012 at 02:52:31PM +0200, Shane Kerr wrote:
> So, for example, we assuming we want to transfer a zone, we could do
> something like:
> 
>     SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records 
>         WHERE zone_id = ?1
>         ORDER by rname, rdtype
> 	LIMIT 100 
> 
> This would give us no more than 100 rows. 
> 
> We can then call again, using the last rname and rdtype, like this:
> 
>     SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records 
>         WHERE (zone_id = ?1) AND (rname >= ?2) AND (rdtype >= ?3)
>         ORDER by rname, rdtype
> 	LIMIT 100 
> 
> We continue this until we get less than our maximum 100 rows back.
> 
> There is a bit of a corner case when our 100th record is in the middle
> of the same rname/rdtype (which is why I have >= and not > there).

There is a LIMIT syntax supported by PostgreSQL where we can provide
the starting row too, so something like:

SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 1
SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 101
SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 201

http://www.postgresql.org/docs/8.1/static/queries-limit.html

With prepared statements, hopefully the query planning will happen just
once and the performance difference may not be too noticable (esp.  if
we use a largish limit).


Kind regards,

Mukund