Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You can see there only the results of the latest statement. A possibility could be that you export the output and commit afterwards. By the way, what is your plan with this refcursor thing? I've never felt the need for it so far :) –  dezso Nov 7 '13 at 21:42
    
@dezso I need to use dynamic SQL, because I am required to return a flexible list of columns, submitted as a parameter. So far I only know how to accomplish that with refcursors. Do you have a better idea? –  AlexKuznetsov Nov 7 '13 at 22:15
    
Please define "keep a result". What are you trying to do exactly? Most probably there is a (superior) solution without explicit cursors. –  Erwin Brandstetter Nov 7 '13 at 23:01
add comment

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
+1 This fits the question perfectly. It may be worth mentioning: Using WITH HOLD, the cursor can continue to be accessed by subsequent transactions in the same session. Effectively, the rows for the cursor are copied to an internal temporary table. I would rather create a temporary table myself. Same performance, more flexible. –  Erwin Brandstetter Nov 8 '13 at 16:19
    
+1 This is exactly what I was looking for. –  AlexKuznetsov Nov 8 '13 at 21:44
add comment

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
share|improve this answer
    
+1 I have an explicit requirement to let users choose the columns to select. It may be rare for you, but it is kind of common for me. –  AlexKuznetsov Nov 8 '13 at 21:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.