Postgresql – How to keep a result set without keeping the transaction open

cursorspgadminpostgresqlpostgresql-9.3

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.

Best Answer

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

Related Solutions

PostgreSQL – How to Select a Dynamic List of Columns

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || '
 FROM   prices
 WHERE  ...
 ORDER  BY ...';

END
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE t (col1, int, col2, date);
SELECT * FROM select_prices(NULL::t, 'col1, col2')

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2')

If you need multiple tables in the same sessions, employ a sequence to get unique names. Related answer on SO:
Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.
SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;

PostgreSQL – Primary Key Disappears from Test Table

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

Drop and recreate the table.
Drop and recreate the schema or database.
(Temporarily) set log_statement or other relevant settings so the statement is not logged.
Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
Edit the log files.
etc.

Best Answer

Related Solutions

PostgreSQL – How to Select a Dynamic List of Columns

PostgreSQL – Primary Key Disappears from Test Table

Related Question