How can I keep a result set without keeping the transaction open?

The following documentation describes how to see the refcursor returned from a function, here, like this:

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

This works for me. However, if I want to keep the results on my screen, I have to keep the transaction open. When I execute COMMIT, my result set is discarded. When I execute both FETCH and COMMIT at the same time, the first result set is discarded.

Is there a way to commit the transaction but keep the result set? The version of PgAdmin is 1.18.1.

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc:


WITH HOLD specifies that the cursor can continue to be used after the
transaction that created it successfully commits

On the other hand, a refcursor opened by a plpgsql function is closed at the end of the transaction. Quoting the plpgsql doc:


All portals are implicitly closed at transaction end. Therefore a
refcursor value is usable to reference an open cursor only until the
end of the transaction.

To create a cursor in a plpgsql function that may be used outside of its "parent" transaction, it's just a matter of syntax. You want the SQL implementation of a cursor, not the plpgsql variant. For this, EXECUTE must be used.

As an example, here's the skeleton of a function similar to yours, but using SQL-level cursors that outlive the transaction:

CREATE FUNCTION dyncursor(name text) RETURNS VOID AS
$
DECLARE
  query text;
BEGIN
  query='SELECT 1 as col1, 2 as col2'; -- sample query
  EXECUTE 'DECLARE ' || quote_ident(name) || ' CURSOR WITH HOLD FOR ' || query;
END
$ language plpgsql;

Demo:

test=> begin;
test=> select dyncursor('foo');
dyncursor
-----------

(1 row)
test=> commit;
test=> fetch all from foo;
col1 | col2
------+------
1 | 2
(1 row)
test=> close foo;
CLOSE CURSOR

StackExchange Database Administrators Q#52968, answer score: 8