Re: How to get the total number of rows with a query

Or maybe you could just execute the full query (no limit, no offset),
and you can get the whole row count using PQntuples (C), pg_num_rows
(php), etc.

When you iterate the resultset to show the rows, you just show the rows
that belong to the showed page, and skip the rest.

On Wed, 2003-08-13 at 18:53, Ron Johnson wrote:

> On Wed, 2003-08-13 at 08:43, krystoffff wrote:
> > Hi
> >
> > I would like to paginate the results of a query on several pages. So I
> > use a query with a limit X offset Y to display X results on a page,
> > ok.
> >
> > But for the first page, I need to run the same query with a count(*)
> > to know how many pages I will get (number total of rows/ X).
> >
> > The problem is my query is very slow (maybe 5s) because there is much
> > worch to do, and on the first page, I need to run this query twice
> > (not exactly, but ...) so the page is very very slow to load.
> >
> > My question is : is there a function to get the total number of rows
> > even on a query with "limit" ? Or what could I do else ?
>
> Presuming that this is your own app, and not psql, why not suck the
> result set into a doubly linked list (or dynamic list, if you use
> Python, Perl, etc)?
>
> There's also the possibility of "chunked buffers", where you malloc,
> say, 8KB before the query runs, and when that gets full, realloc
> to add more space, and continue until the query completes.