Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

use a dynamic select in the web page

$1 = 10
$2 = 5

select * from mytable limit $1 OFFSET $2

--- On Fri, 6/27/08, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
From: Bill Thoen <bthoen(at)gisnet(dot)com>
Subject: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
To: pgsql-general(at)postgresql(dot)org
Date: Friday, June 27, 2008, 8:14 PM

What I want to do is present the results of a query in a web page, but
only 10 rows at a time. My PostgreSQL table has millions of records and
if I don't add a LIMIT 10 to the SQL selection, the request can take too
long. The worst case scenario is when the user requests all records
without adding filtering conditions (e.g. SELECT * FROM MyTable;) That
can take 10-15 minutes, which won't work on a web application.

What I'm wondering is how in PostgreSQL do you select only the first 10
records from a selection, then the next 10, then the next, and possibly
go back to a previous 10? Or do you do the full selection into a
temporary table once, adding a row number to the columns and then
performing sub-selects on that temporary table using the row id? Or do
you run the query with Limit 10 set and then run another copy with no
limit into a temporary table while you let the user gaze thoughtfully at
the first ten records?

I know how to get records form the database into a web page, and I know
how to sense user actions (PageDown, PageUp, etc.) so I'm basically
looking for techniques to extract the data quickly.

Also, if this isn't the best forum to ask this sort of question, I'd
appreciate being pointed to a more appropriate one.

TIA,

- Bill Thoen