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

You will 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 the 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;

The first variant declares an unbound cursor that needs to be bound to a query at OPEN time. The two remaining variants declare a cursor bound to a query.

Note

You can read a good technical overview on how to use cursors in PL/pgSQL functions from the official PostgreSQL documentation at http://www.postgresql.org/docs/current/static/plpgsql-cursors.html.

One thing to note about the documentation is that you don't really need to "return" the cursor, at least not now because cursors can also be passed back to the caller in OUT parameters.

The PostgreSQL documentation states:

"The following example shows one way to return multiple cursors from a single function:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.

BEGIN;
SELECT * FROM myfunc('a', 'b'),
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;"

You can also write the myfunc function using the OUT parameters:

CREATE FUNCTION myfunc2(cur1 refcursor, cur2 refcursor)
RETURNS VOID AS $$
BEGIN
    OPEN cur1 FOR SELECT * FROM table_1;
    OPEN cur2 FOR SELECT * FROM table_2;
END;
$$ LANGUAGE plpgsql;

You will still run the function in exactly the same way as the function returning the cursor variable.

Iterating over cursors returned from another function

To wrap up our cursors' discussion, let's go through an example of returning a cursor and then iterating over the returned cursor in another PL/pgSQL function:

  1. First, let's create a five-row table and fill it with data:
    CREATE TABLE fiverows(id serial PRIMARY KEY, data text);
INSERT INTO fiverows(data) VALUES ('one'), ('two'),
                     ('three'), ('four'), ('five'),
  2. Next, let's define our cursor returning function. This function will open a cursor for a query, based on its argument and then return that cursor:
    CREATE FUNCTION curtest1(cur refcursor, tag text) 
  RETURNS refcursor 
AS $$
BEGIN
    OPEN cur FOR SELECT id, data || '+' || tag FROM fiverows;
    RETURN cur;
END;
$$ LANGUAGE plpgsql;
  3. Next, we define a function, which uses the function we just created to open two additional cursors, and then process the query results. To show that we are not cheating and that the function really creates the cursors, we use the function twice and iterate over the results in parallel:
    CREATE FUNCTION curtest2(tag1 text, tag2 text) 
  RETURNS SETOF fiverows 
AS $$
DECLARE
    cur1 refcursor;
    cur2 refcursor;
    row record;
BEGIN
    cur1 = curtest1(NULL, tag1);
    cur2 = curtest1(NULL, tag2);
    LOOP
        FETCH cur1 INTO row;
        EXIT WHEN NOT FOUND ;
        RETURN NEXT row;
        FETCH cur2 INTO row;
        EXIT WHEN NOT FOUND ;
        RETURN NEXT row;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Please note, that once a record variable inside a plpgsql function is defined by being used, it can't be changed from record type for the duration of that session because PL/pgSQL stores and reuses the plan built with the record type.

By passing in NULL to the first parameters of curtest1, PostgreSQL automatically generates the cursor names so that multiple invocations of this function will not get name conflicts with any other functions, which also create cursors.

Wrapping up of functions returning cursors

The pros of using cursors are as follows:

  • Cursors are a useful tool if you don't want to always execute the query and wait for the full result set before returning from a function
  • Currently, they are also the only way to return multiple result sets out of a user-defined function

The cons of using cursors are as follows:

  • They mainly work to pass data between functions on the server, and you are still limited to one record set per call returned to the database client
  • They are sometimes confusing to use, and bound and unbound cursors are not always interchangeable

Note

You can read more about cursors at http://www.postgresql.org/docs/current/static/plpgsql-cursors.html.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.129.148.4