Thread: How can I retrieve a function result?

How can I retrieve a function result?

From
"Luis Alberto Pérez Paz"
Date:
Hi pgsql community,
 
I'm using libpq C. I'm trying to execute a FUNCTION called "myFunction",
 
*********************************************************************************************************
PGResult *res;
res = PQexecParams ( conn, "select myFunction($1,$2,$3)" , 3, NULL, paramValues, paraLenghts, paramFormats, resultFormat);
 
if ( PQresultStatus (res) != PGRES_TUPLES_OK)
{
//error
}else{
//ok
}
 
*********************************************************************************************************
 
It's works fine, however I dont know how can I retrieve the result that return the FUNCTION "myFunction". "myFunction" is a postgres FUNCTION which returns a INT.
 
Any advices??
 
thanks in advance.
 
Luis.


--
paz, amor y comprensión
        (1967-1994)

Re: How can I retrieve a function result?

From
Joachim Wieland
Date:
Luis,

On Mon, Jun 12, 2006 at 06:24:24PM -0500, Luis Alberto Pérez Paz wrote:
> res = PQexecParams ( conn, "select myFunction($1,$2,$3)" , 3, NULL,
> paramValues, paraLenghts, paramFormats, resultFormat);

> It's works fine, however I dont know how can I retrieve the result that
> return the FUNCTION "myFunction". "myFunction" is a postgres FUNCTION which
> returns a INT.

libpq functions for retrieving query results are described here:

http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO
Have you tried them and do you have a special problem with one of those? If
so, please give more detail.

Getting the result of a function does not differ from getting the result of
a regular select of a table or view.


Joachim


Re: How can I retrieve a function result?

From
"Luis Alberto Pérez Paz"
Date:
Hi,
 
Thanks a lot for your answer,
 
Let me give you a simple example with more detail:
 
In my postgres database I have a function called "myFunction".
 
/************************************************************************/
CREATE OR REPLACE FUNCTION myFunction(INT) RETURN INT AS '
DECLARE
  indice    ALIAS FOR $1;
BEGIN
 
  IF indice > 0 THEN
    return -900;
  ELSE
    //something to do....
    return 0;
  END IF;
 
END;
' LANGUAGE 'plpgsql';
/************************************************************************/
 
I have a program in C/C++ which call the FUNCTION 'myFunction'
 
/************************************************************************/
res = PQexecParams ( conn, "select myFunction($1)" , 1, NULL,
paramValues, paraLenghts, paramFormats, resultFormat);
/************************************************************************/
 
The program works fine, actually I can verify that it executes the FUNCTION 'myFunction', however I dont know how can I get the return value of the FUNCTION 'myFunction' (as you can see in the little example the return value can be 0 or -900).
 
 
Regards,
 
Thanks in advance!
 
 


 
On 6/12/06, Joachim Wieland <joe@mcknight.de> wrote:
Luis,

On Mon, Jun 12, 2006 at 06:24:24PM -0500, Luis Alberto Pérez Paz wrote:
> res = PQexecParams ( conn, "select myFunction($1,$2,$3)" , 3, NULL,
> paramValues, paraLenghts, paramFormats, resultFormat);

> It's works fine, however I dont know how can I retrieve the result that
> return the FUNCTION "myFunction". "myFunction" is a postgres FUNCTION which
> returns a INT.

libpq functions for retrieving query results are described here:

http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO
Have you tried them and do you have a special problem with one of those? If
so, please give more detail.

Getting the result of a function does not differ from getting the result of
a regular select of a table or view.


Joachim




--
paz, amor y comprensión
        (1967-1994)

Re: How can I retrieve a function result?

From
Joachim Wieland
Date:
Luis,

On Tue, Jun 13, 2006 at 01:19:32PM -0500, Luis Alberto Pérez Paz wrote:
> The program works fine, actually I can verify that it executes the FUNCTION
> 'myFunction', however I dont know how can I get the return value of the
> FUNCTION 'myFunction' (as you can see in the little example the return
> value can be 0 or -900).

this is similar to retrieving the result of a query that returned one row
and one column. So you just have to use PQgetvalue(res, 0, 0).

Here are a few other examples:

Check if there were rows at all:

        if (PQntuples(res) == 0) {
                /* no rows */
                PQclear(res);
                return (char*) 0;
        }

Check if there was anything else than one column per row:

        if (PQnfields(res) != 1) {
                /* did not get only 1 column back */
                PQclear(res);
                return (char*) 0;
        }

Check whether or not the first column, first row field is NULL:

        if (PQgetisnull(res, 0, 0)) {
                /* got NULL */
                PQclear(res);
                return (char*) 0;
        }

Get the first row, first column value as char*:

        db_value = PQgetvalue(res, 0, 0);


I hope this gives you an idea.

Those functions are in this section of the documentation:

http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

Joachim

Re: How can I retrieve a function result?

From
"Luis Alberto Pérez Paz"
Date:
Thank you Joachim,
it woks!
 
 
Regards,
 
Luis A Perez Paz
 


 
On 6/13/06, Joachim Wieland <joe@mcknight.de> wrote:
Luis,

On Tue, Jun 13, 2006 at 01:19:32PM -0500, Luis Alberto Pérez Paz wrote:
> The program works fine, actually I can verify that it executes the FUNCTION
> 'myFunction', however I dont know how can I get the return value of the
> FUNCTION 'myFunction' (as you can see in the little example the return
> value can be 0 or -900).

this is similar to retrieving the result of a query that returned one row
and one column. So you just have to use PQgetvalue(res, 0, 0).

Here are a few other examples:

Check if there were rows at all:

       if (PQntuples(res) == 0) {
               /* no rows */
               PQclear(res);
               return (char*) 0;
       }

Check if there was anything else than one column per row:

       if (PQnfields(res) != 1) {
               /* did not get only 1 column back */
               PQclear(res);
               return (char*) 0;
       }

Check whether or not the first column, first row field is NULL:

       if (PQgetisnull(res, 0, 0)) {
               /* got NULL */
               PQclear(res);
               return (char*) 0;
       }

Get the first row, first column value as char*:

       db_value = PQgetvalue(res, 0, 0);


I hope this gives you an idea.

Those functions are in this section of the documentation:

http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

Joachim



--
paz, amor y comprensión
        (1967-1994)