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

Re: 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: Re: Calling stored function that returns a cursor from a libpq program
Date: 2009-08-13 16:04:37
Message-ID: 92265adf0908130904q47847001qf71d8243db272795@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-08-13 16:43:49
Subject: Re: Calling stored function that returns a cursor from a libpq program
Previous:From: Tom LaneDate: 2009-08-13 15:30:38
Subject: Re: Calling stored function that returns a cursor from a libpq program

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