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

Question

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.

Answer 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

Answer 2

pgAdmin is just a GUI. Largely irrelevant to this question. It just so happens that one or more sessions are bound to an SQL editor window and end when the window is closed.

If you want to keep the transaction open, just don't COMMIT (or ROLLBACK), yet.

If you really want to "keep" tha result set, write it to a table - possibly a TEMPORARY or UNLOGGED table if you don't need to persist permanently. A temporary table lives and dies with the session by default.

SQL and PL/pgSQL are rather strict with their type system and some things that seem possible are not implemented (yet). But what you are trying to do can probably be solved without cursors. If you need to provide a table name to a function dynamically, use a polymorphic return type that's chained to an input parameter:

CREATE OR REPLACE FUNCTION f_dynamic_select(_tbl anyelement)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT * FROM ' || pg_typeof(_tbl);

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM  f_dynamic_select(NULL::my_schema.my_table)

This form is automatically safe against SQL injection, since pg_typeof() returns a regtype value that is automatically escaped (if necesaray) when it's automatically converted to text during string concatenation.

I have written a closely related answer dealing with polymorphic types just yesterday. It has more explanation and links:
Insert values from a record variable into a table

Normally, a plain and simple SELECT would do the job. It's a rare condition that table names have to be provided dynamically.

SELECT * FROM my_schema.my_table