Re: Pagination - 1 or 2 queries?

On Fri, 5 Sep 2003, CSN wrote:

>
> --- "scott.marlowe" <scott.marlowe@ihs.com> wrote:
> > begin;
> > declare bubba as cursor for select * from table
> > order by fieldname;
> > move forward 100 in bubba;
> > fetch 5 from bubba;
> > rollback;
> >
> > Then you get the same kind of effect, but only 5
> > rows have to be retrieved
> > from the database to the client, and pg_fetch_array
> > will now iterate over
> > those 5 rows only, and then run dry, so to speak.
>
> Actually, with this method would you be able to get
> the count of all rows that could be returned (not just
> the 5)?

My previous one about using absolute count was wrong, btw, so you can
either fetch forward all and get the count that returns or run select
count(*).  note that if you fetch forward all on a complex query, you may
NOT be able to fetch backward all since cursors have a hard time going
backwards on complex queries.