Postgres Pro
Downloads
Home   >   mailing lists

Thread: Calling stored function that returns a cursor from a libpq program

Calling stored function that returns a cursor from a libpq program

From
Mazen Abdel-Rahman
Date:
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

Re: Calling stored function that returns a cursor from a libpq program

From
Tom Lane
Date:
Mazen Abdel-Rahman <saba.mazen@gmail.com> writes:
> I am trying to use a stored functions that returns a CURSOR in a C
> program that uses that libpq library.

It looks like you've just hardwired an assumption about what the name of
the cursor will be.  It'd be better to pay attention to the name
returned by the function.

It also looks like you're not bothering to check that the BEGIN command
succeeded.  If it didn't for some reason, that could explain the
failure.

            regards, tom lane

Re: Calling stored function that returns a cursor from a libpq program

From
Mazen Abdel-Rahman
Date:
Hi Tom,

Thank you for the quick reply.

I put in a check to see what the BEGIN transaction command returns - and it seems to be successful as it returns    "PGRES_COMMAND_OK".

I also look at the result returned from calling the stored function and use that name in the fetch command - however I am still getting the following error message:

" cursor "portalname" does not exist  "

I am able to use cursors successfully in my program if I retrieve the CURSOR directly and not through a stored function - for example using the line:

char * cursorCall = "DECLARE myPortal CURSOR for select * from cars";


My problem is with using CURSOR returned by stored functions.

Here my modified code to check the being transaction result and to use the name returned by the call to the stored function in the fetch command:

//BEGIN the transaction

queryResult4 = PQexec(connection, "BEGIN");

execStatus = PQresultStatus(queryResult4);  //Get the result status

char * beginTransactionErrorMessage = PQresultErrorMessage(queryResult4);

NSLog([NSString stringWithCString:beginTransactionErrorMessage]);


char* cursorCall = "select reffunc('portalName')";   //call stored function to get the cursor 

//Call the stored function that returns a CURSOR

queryResult4 = PQexec(connection, cursorCall);

execStatus = PQresultStatus(queryResult4);  //Get the result status

char * errorMessage = PQresultErrorMessage(queryResult4);

NSLog([NSString stringWithCString:errorMessage]);

if ((execStatus == PGRES_COMMAND_OK) || (execStatus == PGRES_TUPLES_OK))  {  //cursor retrieved succesfully

int numRows;  //variable to store number of rows returned in query

int numColumns;  //number of columns per row

numRows = PQntuples(queryResult4);

NSLog(@"The stored function call returned %i rows.", numRows);

numColumns =  PQnfields(queryResult4);  //get number of columns

//get the name of the returned cursor

char * cursorReturned;

cursorReturned = PQgetvalue(queryResult4, 0, 0);

NSString * fetchNextQuery = [NSString stringWithFormat:@"FETCH ALL in %s", cursorReturned];

queryResult4 = PQexec(connection,[fetchNextQuery UTF8String] );


execStatus = PQresultStatus(queryResult4);  //Get the result status

char * errorMessage2 = PQresultErrorMessage(queryResult4);

NSLog([NSString stringWithCString:errorMessage2]); ....




Thanks!
Mazen Abdel-Rahman


On Thu, Aug 13, 2009 at 8:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mazen Abdel-Rahman <saba.mazen@gmail.com> writes:
> I am trying to use a stored functions that returns a CURSOR in a C
> program that uses that libpq library.

It looks like you've just hardwired an assumption about what the name of
the cursor will be.  It'd be better to pay attention to the name
returned by the function.

It also looks like you're not bothering to check that the BEGIN command
succeeded.  If it didn't for some reason, that could explain the
failure.

                       regards, tom lane

Re: Calling stored function that returns a cursor from a libpq program

From
Tom Lane
Date:
Mazen Abdel-Rahman <saba.mazen@gmail.com> writes:
>  //get the name of the returned cursor

> char * cursorReturned;

> cursorReturned = PQgetvalue(queryResult4, 0, 0);

>  NSString * fetchNextQuery = [NSString stringWithFormat:@"FETCH ALL in %s",
> cursorReturned];

I'd try double quoting that, ie
    stringWithFormat:@"FETCH ALL in \"%s\"",

As-is you're risking trouble with mixed-case names, which is what you
seem to be using.

            regards, tom lane