O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required