Returning Structured Data - PostgreSQL Server Programming - page 110

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;

FETCH ALL FROM b;

COMMIT;"

You could also write the function myfunc using 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 would still run the function exactly the same 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');