Returning cursors

Another method of getting a tabular data out of function is by using a CURSOR.

CURSOR, or a portal as it is sometimes referenced in PostgreSQL documentation, is an internal structure which 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 as much data as needed for each FETCH from the cursor.

In plain SQL, CURSOR is defined as follows:

DECLARE mycursor CURSOR  FOR <query >;

And later the rows are fetched using the following statement:

FETCH NEXT FROM  mycursor;

While you can use a cursor to handle the data from a set returning function the usual way, by simply declaring the cursor as DECLARE mycursor CURSOR FOR SELECT * FROM mysetfunc();, it is many times more beneficial to have the function itself just return a cursor.

You would want...