Calling a function that returns a refcursor

Question

I am using Postgresql 8.3 and have the following simple function that will return a refcursor to the client

CREATE OR REPLACE FUNCTION function_1() RETURNS refcursor AS $$
DECLARE
        ref_cursor REFCURSOR;
BEGIN
        OPEN ref_cursor FOR SELECT * FROM some_table;
        RETURN (ref_cursor);    
END;
$$ LANGUAGE plpgsql;

Now , I can use the following SQL commands to call this function and manipulate the returned cursor ,but the cursor name is automatically generated by the PostgreSQL

BEGIN;
SELECT function_1();  --It will output the generated cursor name , for example , "<unnamed portal 11>" ;
FETCH 4   from  "<unnamed portal 11>"; 
COMMIT;

Besides explicitly declaring the cursor name as the input parameter of the function as described by 38.7.3.5. Returning Cursors, can I declare my own cursor name and use this cursor name to manipulate the returned cursor instead of Postgresql automatically generates for me ?

If not, are there any commands that can get the generated cursor name ?

VoidMain · Answer 1 · 2013-05-15T13:54:03.183

18

I'm not quite sure from wich version of Postgre this is available (in 8.4 it is valid) but i found quite easiest to define the cursor name when you declare it, like this:

CREATE OR REPLACE FUNCTION function_1() RETURNS refcursor AS $$
DECLARE
        ref_cursor REFCURSOR := 'mycursor';
BEGIN
        OPEN ref_cursor FOR SELECT * FROM some_table;
        RETURN (ref_cursor);    
END;
$$ LANGUAGE plpgsql;

And then you can get it like this:

BEGIN;
SELECT function_1();
FETCH 4   from  mycursor; 
COMMIT;

I find this method less cumbersome. Hope that helps.

edited May 15 '13 at 13:54

answered Sep 18 '12 at 18:54

VoidMain

1,987
20
22

2

Thanks for the solution. It is also available in 8.3.12 – Nenad May 14 '13 at 21:36

Grzegorz Szpetkowski · Accepted Answer · 2011-07-13T09:10:38.857

6

Yes, use:

CREATE OR REPLACE FUNCTION function_1(refcursor) RETURNS refcursor AS $$
BEGIN
        OPEN $1 FOR SELECT * FROM some_table;
        RETURN $1;    
END;
$$ LANGUAGE plpgsql;

Result:

SELECT function_1('myowncursorname');
   function_1
-----------------
 myowncursorname
(1 row)

It looks like auto-generated name is <unnamed portal n>, where n is natural number (from 1).

EDIT:

As another way you could use pg_cursors view with such query to obtain generated cursor name:

SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';

For example:

BEGIN;
SELECT function_1();
SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';
COMMIT;

Result:

     function_1
--------------------
 <unnamed portal 3>
(1 row)

        name
--------------------
 <unnamed portal 3>
(1 row)

edited Jul 13 '11 at 09:10

answered Jul 13 '11 at 08:15

Grzegorz Szpetkowski

36,988
6
90
137

Thanks . But the problem is that the `function_1` is provided by other parties and I cannot change the input parameters for this function. – Ken Chan Jul 13 '11 at 08:18
I think it's only way to provide custom cursor name, however I am not 100% sure with that. – Grzegorz Szpetkowski Jul 13 '11 at 08:24
@Ken: What about fetching cursor's name from `pg_cursors` system catalog (as above) ? – Grzegorz Szpetkowski Jul 13 '11 at 18:17
Nice try and thanks! It can get the cursor name from the `pg_cursors` view .But my `statement` is so long , so I use the latest `creation_time` of the `pg_cursors` to get the name of the curosr – Ken Chan Jul 13 '11 at 18:25

Calling a function that returns a refcursor

2 Answers2

Linked