Hi All,
I am trying to use a stored functions that returns a CURSOR in a C
program that uses that libpq library.
I have the following two example stored functions:
CREATE OR REPLACE FUNCTION reffunc(cursorName refcursor) RETURNS
refcursor AS $$
BEGIN
OPEN cursorName FOR SELECT * FROM cars;
RETURN cursorName;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION reffunc2() RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT * FROM cars;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
Here is a section of the code I am using to try to use the stored
function reffunc(cursorName refcursor) above (it is in Objective C
on Mac OS X):
************************************************************************
********************
************************************************************************
********************
//Start the transation
queryResult4 = PQexec(connection, "BEGIN");
//string that makes a call to the stored function
char* cursorCall = "select reffunc('mazPortal')";
//Get the result
queryResult4 = PQexec(connection, cursorCall);
//Check the result status
execStatus = PQresultStatus(queryResult4);
//**The result status returned is PGRES_TUPLES_OK
if ((execStatus == PGRES_COMMAND_OK) || (execStatus ==
PGRES_TUPLES_OK)) {
//Now I try to get the first row
queryResult4 = PQexec(connection, "FETCH next in mazPortal");
//Get the status
execStatus = PQresultStatus(queryResult4); //Get the result status
//** this is where it fails. Status returned is PGRES_FATAL_ERROR
//and the associated text with it is "ERROR: cursor "mazportal"
does not exist"
.....
.....
.....
************************************************************************
********************
************************************************************************
********************
Does anyone know the proper way to call a stored function that
returns a CURSOR from a C program that utilized the libpq library? And then how do I retrieve the records of that cursor? (i.e. FETCH ALL, FETCH NEXT, etc.)
Or is it not possible?
Thanks for our help!
Mazen Abdel-Rahman
