cursors

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. (However, PL/pgSQL users do not normally need to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.

BEGIN;
 
-- declare/open a cursor and define a query
DECLARE myportal cursor FOR SELECT * FROM TABLE;
 
-- get the first 50 records (would seem like adding LIMIT 50 to above query)
fetch 50 IN myportal;
 
-- repeat as many times as necessary to keep fetching 50 more records
 
fetch backward 50 IN myportal;
 
END;