Devart Forums

I'm trying to figure out how to get a datareader for a refcursor returned by a plpgsql proc:

CREATE OR REPLACE FUNCTION test_reffunc()
RETURNS refcursor AS
$BODY$
DECLARE
v_refcursor refcursor;

BEGIN
OPEN v_refcursor FOR SELECT col1 FROM test;
RETURN v_refcursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc() OWNER TO postgres;

However, the IDataReader result from ExecuteReader just returns the name of the cursor: in a single row. Is there a way to have the fetch all accomplished by the corelab component on this cursor ? I am using the enteprise library components.

If you execute your function in pgAdmin, you'll get the same results, so this is not PostgreSQLDirect .NET problem. For information on how to work with refcursors please refer to PostgreSQL help documentation.

Hi there brad! Still there? Maybe I can help you on your problem.. I don't know if the current version of corelab's component is capable of retrieving a refcursor's resultset, but i think you are still using their previous releases.

Anyway, about your problem, you can actually solve that by having 2 commands, 1 to execute your stored procedure that returns a refcursor, and the other 1 to fetch the results from the refcursor. But you must put these inside a transaction, since refcursor must be within a transaction to function properly (based on postgres documentation).. So based on your example, we can code this in VB.Net :


dim trans as pgsqltransaction ' must be on a transaction
trans = conn.begintransaction()

dim command1 as new pgsqlcommand("test_reffunc",conn)
command1.commandtype = commandtype.storedprocedure
command1.prepare()
command1.executenonquery()

dim command2 as new pgsqlcommand("Fetch all from v_refcursor",conn)
dim reader as pgsqldatareader = command2.executereader()

'-- do some code here --'

trans.commit ' <-- dont forget to commit

' -- Continue your code here --'

Hope this helps..

To the corelab team, hope you had done something about this issue..

Hello, bradk.
Does this code help you. It produces an error on my end (cursor "v_refcursor" does not exist).

You will have to revise a slight change on the code for it to work properly. Just find a way to call the refcursor's name. To be able to do this, you should change your stored procedure code like this:

CREATE OR REPLACE FUNCTION test_reffunc(ref_cursor)
RETURNS refcursor AS
$BODY$

BEGIN
OPEN $1 FOR SELECT col1 FROM test;
RETURN $1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc(ref_cursor) OWNER TO postgres;


Then change your code in .net like this:

dim command1 as new pgsqlcommand("Select * from test_reffunc('v_refcursor')")

Hope this code help you...

What is "ref_cursor"? If I execute this creation statement with pgAdmin, I get the error: "type ref_cursor does not exist". Please advise.

Ok, sorry.. Its just refcursor ... Remove the _ ...

This time it works.
Thanks for your consideration.