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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)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
>