From: | Philip Hallstrom <philip(at)adhesivemedia(dot)com> |
---|---|
To: | Charles Hauser <chauser(at)acpub(dot)duke(dot)edu> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: CURSOR/FETCH vs LIMIT/OFFSET |
Date: | 2002-05-08 20:31:39 |
Message-ID: | 20020508133050.A20473-100000@teak.adhesivemedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Why not at the very beginning do something like "SELECT COUNT(*)..." and
store that as $totalTuples... then use limit/offset...
-philip
On Wed, 8 May 2002, Charles Hauser wrote:
> Hi,
>
> re: displaying results of query.
>
> Trying to work out details of how to let users page back and forth
> among a multi-page list of results.
>
> Problem: I need to know the total number of tuples the query would
> return in order to decide if there are more to display.
>
> 1) Use CURSOR and FETCH
>
> $res= $con->exec(
> "BEGIN WORK;
> DECLARE gene_result CURSOR FOR
> SELECT blah blah
> ORDER BY blah;
> FETCH $offset IN gene_result
> ");
>
> $result->ntuple: returns only the number set by $offset, not TOTAL for query.
>
> 2) use LIMIT OFFSET
>
> same problem,
> $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> TOTAL for query.
>
> So there has to be a way to glean both the TOTAL and the SUBSET returned?
>
> Searching the postgresql archives obviously can deal with this
> http://archives.postgresql.org/pgsql-sql/
> 'Displaying documents 11-20 of total 243 found.'
>
> --
> Regards,
>
> Chuck
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-05-08 20:40:46 | Bad performance on update from a join |
Previous Message | Holger Marzen | 2002-05-08 20:01:05 | Re: Performance issues with compaq server |