Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group