Database Reference
In-Depth Information
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 to do this if you need different cursors based on argument values, or
if you need to return dynamically structured data out of a function without defining the
structure when calling the function.
The cursor in PL/pgSQL is represented by a variable of type
refcursor
and must
be declared in one of the following three ways:
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM
tenk1 WHERE unique1 = key;
