Re: displaying records from X to Y

On Wed, 2002-10-16 at 05:10, Josh Berkus wrote:
> Mel,
>
> > => select * from foo_table limit 100;
> > OR
> > => select * from foo_table order by foo_column desc limit 100;
>
> Easy:
>
> SELECT * FROM foo_table LIMIT 100 OFFSET 100;
>
> -Josh Berkus

If the query is complex and time-consuming, you might do better to use a
cursor; then the query is done once and you can fetch results from it at
will.  Using LIMIT and OFFSET requires the whole query to run every
time.  If you are running it in response to an interactive request for
the next chunk of data, the necessary delay is obviously undesirable.
(Another problem, if you are not in a transaction, is that rows may be
added or deleted by other sessions in between your commands, which may
cause gaps or duplications in the records you see)

Use a cursor like this:

    BEGIN;   -- cursors must operate in a transaction
    DECLARE mycursor CURSOR FOR
        SELECT * FROM complex_view;
    FETCH 100 FROM mycursor;         -- first 100 rows
    FETCH 100 FROM mycursor;         -- next 100
    FETCH NEXT FROM mycursor;        -- next row
    FETCH BACKWARD 10 FROM mycursor; -- previous 10 rows (reversed)
    END;    -- end transaction and close cursor

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But be ye doers of the word, and not hearers only,
      deceiving your own selves."              James 1:22