PostgreSQL Server Programming - Second Edition

Returning cursors

Another method that can be used to get tabular data out of a function, is using CURSOR.

CURSOR, or a portal, as it is sometimes referred to in PostgreSQL documentation, is an internal structure that contains a prepared query plan, ready to return rows from the query. Sometimes, the cursor needs to retrieve all the data for the query at once, but for many queries it does lazy fetching. For example, queries that need to scan all of the data in a table, such as SELECT * FROM xtable, only read the amount of data that is needed for each FETCH from the cursor.

In plain SQL, CURSOR is defined as follows:

DECLARE mycursor CURSOR  FOR <query >;

Later, the rows are fetched using the following statement:

FETCH NEXT FROM  mycursor;

While you can ...