sqlpostgresqlplpgsqldatabase-cursorref-cursor

Calling a function that returns a refcursor


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 ?


Solution

  • 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)