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 <[EMAIL PROTECTED]> wrote: From: Bill Thoen <[EMAIL PROTECTED]> Subject: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks To: pgsql-general@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
