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