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 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();
, many times it is more beneficial to have the function itself just return...