Hi all,

This is the situation:  You are doing a big query, but you want the results
on the web page to be paginated.  ie. The user can click page 1, 2, etc.

So, you need know how many rows total would be returned, but you also only
need a small fraction of them.

What is an efficient way of doing this?

It seems to me that using a CURSOR would be advantageous, however once a
CURSOR is opened, how do you get the full row count?

ie. Can you do this:?

1. Declare a cursor
2. Find the total number of rows returned
3. Fetch the subset of the rows that are required
4. Construct a pagination based on the info from 2 and 3.

If this can't be done - how do you do it?  Is the only way to repeat the
whole query twice, the first time doing a count(*) instead of the select
variables?

Thanks,

Chris



Mon, 30 Jul 2001 17:07:26 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Portal question
I've used select count(), then a select LIMIT/OFFSET for the pages.. A
cursor might be a better idea though I don't think you can get the total
number of rows without count()'ing them.

Good luck!

-Mitch

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Hackers" <pgsql-hackers@postgresql.org>
Sent: Monday, July 30, 2001 5:07 AM
Subject: [HACKERS] Portal question


> Hi all,
>
> This is the situation:  You are doing a big query, but you want the
results
> on the web page to be paginated.  ie. The user can click page 1, 2, etc.
>
> So, you need know how many rows total would be returned, but you also only
> need a small fraction of them.
>
> What is an efficient way of doing this?
>
> It seems to me that using a CURSOR would be advantageous, however once a
> CURSOR is opened, how do you get the full row count?
>
> ie. Can you do this:?
>
> 1. Declare a cursor
> 2. Find the total number of rows returned
> 3. Fetch the subset of the rows that are required
> 4. Construct a pagination based on the info from 2 and 3.
>
> If this can't be done - how do you do it?  Is the only way to repeat the
> whole query twice, the first time doing a count(*) instead of the select
> variables?
>
> Thanks,
>
> Chris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Mon, 30 Jul 2001 10:42:47 -0400
"Mitch Vincent" <mvincent@cablespeed.com>
Re: Portal question