Calling stored function that returns a cursor from a libpq program

From: Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Calling stored function that returns a cursor from a libpq program
Date: 2009-08-13 15:12:49
Message-ID: 92265adf0908130812p2b83816cufa891930add16e85@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-08-13 15:30:38 Re: Calling stored function that returns a cursor from a libpq program
Previous Message Tom Lane 2009-08-13 14:56:02 Re: ERROR: Too many updates/deletes within transaction (cmin