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.