Re: Need help paging through record sets - Mailing list pgsql-sql

Sort of depends on the nature of your application. You can use offset to get 
specific chunks:

select * from foo order by date limit 100 offset 100;

You should be aware, however, that on a very large table this can be quite 
inefficient as you will have to do the select and sort on the large table 
just to get the next chunk. (With only 600 tuples you will probably have 
everything in memory anyway so it's not really a problem.) Also, if the data 
is actively updated you could get a tuple added or deleted between page views 
which would mess up the offsets and cause someone to miss an item or get a 
duplicate.

If you want to page through a small subset of a large file you can use 
cursors or temporary tables but you will have to be sure your connection 
persistence, session management and such can accomodate such an arrangement.

Cheers,
Steve


On Friday 20 December 2002 12:53 pm, cmccormick@mailsnare.net wrote:
> Hello everyone, and thanks for reading my first newbie post. :-)
>
> I am a neopyhte PHP and postgreSQL user, with a website at
> www.the-athenaeum.org.  We store (among other things) artworks, which
> people can view in a list, sorted by artist name, date, medium, etc.
>
> We now have enough works that I need to rewrite the PHP listings script
> (and its embedded SQL) so that users can page through records.  As an
> example, if a user is looking at works by date ascending, they may want to
> see 100 records at a time.  Since we have 600+ records, there would be 7
> pages.  They'd start on the first page (of course!) and there would be
> links to pages 2 through 7 as well, just like with results pages of a
> Google search.  They could, from page 1, click any of the other pages to go
> immdiately to that set of 100 records for display.
>
> I see this kind of thing all over the place, and in looking it up, I see
> most solutions use "SELECT TOP x", which postgreSQL doesn't seem to have. 
> I know how to use LIMIT, but that always starts from the top.  I could add
> a piece to the WHERE clause, say something like "WHERE date > 01-02-1853",
> but how do I know where the cutoffs are several pages along, without
> retrieving the whole record set?
>
> I suppose the optimal solution for me would be to sort all of the records,
> then be able to select a range from that sorted record set.  So, if they
> click the link to page 3, I'd like to do this (in pseudocode):
>
> 1.  SORT records by the date field, descending
> 2.  Retrieve only records 200-299 from the sorted list
>
> Is there a way to do that?  How is it done elsewhere?
>
> Thanks in advance for your help,
> Chris McCormick, webmaster
> The Athenaeum - Interactive Humanities Online
> www.the-athenaeum.org
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster