Database Reference
In-Depth Information
2. Next, let's define our cursor returning function. This function will open a
cursor for a query based on its argument and then returns 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
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;
