[SOLVED] Libpq: Receiving the value of a procedure's output parameter

Libpq: Receiving the value of a procedure's output parameter

I'm building an application using libpq, and the documentation is not very clear regarding executing calls to procedures and functions in the PostgreSQL database. I have been able to retrieve the return value of a function call via a libpq result, but I'm not sure how to get the value of an output parameter that's not specifically returned by the function or procedure. Is there a way to do this, and if so, how?

Solution

On the SQL level OUT parameters look like 'pass by reference'. However in libpq output parameters and query results are returned the same way. Succesfully calling a procedure with OUT paramesters will set PGRES_TUPLES_OK and return one row containing the values of the output params.

Here is a simple example based on the code in the libpq documentation

First create a procedure to test with:

CREATE PROCEDURE inc_int(INOUT a integer)
LANGUAGE plpgsql
AS $$
BEGIN
a := a+1;
END;
$$;

Then a minimal C program to call that procedure:

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    int         nFields;
    int         i,
                j;

    conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }
    
    /* execute the call to our procedure */

    res = PQexec(conn, " CALL inc_int(1) ");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "CALL procedure failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    
    /* iterate over the resultset - although in this special case
       calling PQgetvalue(res, 0, 0) would do */
    
    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);


    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}

running the resulting program: