Table of Contents Previous Next


6 libpq C Library

Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library.
structs and typedefs
IN/OUT/IN OUT parameters
In earlier releases, Advanced Server provided support for REFCURSORs through the following libpq functions; these functions should now be considered deprecated:
You may now use PQexec() and PQgetvalue() to retrieve a REFCURSOR returned by an SPL (or PL/pgSQL) function. A REFCURSOR is returned in the form of a null-terminated string indicating the name of the cursor. Once you have the name of the cursor, you can execute one or more FETCH statements to retrieve the values exposed through the cursor.
CREATE OR REPLACE FUNCTION getEmployees(p_deptno NUMERIC) RETURN REFCURSOR AS
result REFCURSOR;
BEGIN
OPEN
result FOR SELECT * FROM emp WHERE deptno = p_deptno;

RETURN result;
END;
This function expects a single parameter, p_deptno, and returns a REFCURSOR that holds the result set for the SELECT query shown in the OPEN statement. The OPEN statement executes the query and stores the result set in a cursor. The server constructs a name for that cursor and stores the name in a variable (named result). The function then returns the name of the cursor to the caller.
char *commandText = malloc(commandLength);
PGresult *result;
int row;

sprintf(commandText,
"FETCH ALL FROM \"%s\"", cursorName);

result = PQexec(conn, commandText);

if (PQresultStatus(result) != PGRES_TUPLES_OK)
fail(conn, PQerrorMessage(conn));

printf("-- %s --\n", description);

for (row = 0; row < PQntuples(result); row++)
{
const char *delimiter = "\t";
int col;

for (col = 0; col < PQnfields(result); col++)
{
printf("%s%s",
delimiter, PQgetvalue(result, row, col));
delimiter = ",";
}

printf("\n");
}

PQclear(result);
free(commandText);
}

static void
fail(PGconn *conn, const char *msg)
{
fprintf(stderr, "%s\n", msg);

if (conn != NULL)
PQfinish(conn);

exit(-1);
}
The code sample contains a line of code that calls the getEmployees() function, and returns a result set that contains all of the employees in department 10:
The PQexec() function returns a result set handle to the C program. The result set will contain exactly one value; that value is the name of the cursor as returned by getEmployees().
Once you have the name of the cursor, you can use the SQL FETCH statement to retrieve the rows in that cursor. The function fetchAllRows() builds a FETCH ALL statement, executes that statement, and then prints the result set of the FETCH ALL statement.
The first REFCURSOR contains the name of a cursor (employees) that contains all employees who work in a department within the range specified by the caller.
The second REFCURSOR contains the name of a cursor (departments) that contains all of the departments in the range specified by the caller.
In this example, instead of returning a single REFCURSOR, the function returns a SETOF REFCURSOR (which means 0 or more REFCURSORS). One other important difference is that the libpq program should not expect a single REFCURSOR in the result set, but should expect two rows, each of which will contain a single value (the first row contains the name of the employees cursor, and the second row contains the name of the departments cursor).
As in the previous example, you can use PQexec() and PQgetvalue() to call the SPL function:
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void fetchAllRows(PGconn *conn,
const char *cursorName,
const char *description);
static void fail(PGconn *conn, const char *msg);

int
main(int argc, char *argv[])
{
PGconn *conn = PQconnectdb(argv[1]);
PGresult *result;

if (PQstatus(conn) != CONNECTION_OK)
fail(conn, PQerrorMessage(conn));

result = PQexec(conn, "BEGIN TRANSACTION");

if (PQresultStatus(result) != PGRES_COMMAND_OK)
fail(conn, PQerrorMessage(conn));

PQclear(result);

result = PQexec(conn, "SELECT * FROM getEmpsAndDepts(20, 30)");

if (PQresultStatus(result) != PGRES_TUPLES_OK)
fail(conn, PQerrorMessage(conn));

fetchAllRows(conn, PQgetvalue(result, 0, 0), "employees");
fetchAllRows(conn, PQgetvalue(result, 1, 0), "departments");

PQclear(result);

PQexec(conn, "COMMIT");

PQfinish(conn);

exit(0);
}

static void
fetchAllRows(PGconn *conn,
const char *cursorName,
const char *description)
{
size_t commandLength = strlen("FETCH ALL FROM ") +
strlen(cursorName) + 3;
char *commandText = malloc(commandLength);
PGresult *result;
int row;

sprintf(commandText, "FETCH ALL FROM \"%s\"", cursorName);

result = PQexec(conn, commandText);

if (PQresultStatus(result) != PGRES_TUPLES_OK)
fail(conn, PQerrorMessage(conn));

printf("-- %s --\n", description);

for (row = 0; row < PQntuples(result); row++)
{
const char *delimiter = "\t";
int col;

for (col = 0; col < PQnfields(result); col++)
{
printf("%s%s", delimiter, PQgetvalue(result, row, col));
delimiter = ",";
}

printf("\n");
}

PQclear(result);
free(commandText);
}

static void
fail(PGconn *conn, const char *msg)
{
fprintf(stderr, "%s\n", msg);

if (conn != NULL)
PQfinish(conn);

exit(-1);
}
If you call getEmpsAndDepts(20, 30), the server will return a cursor that contains all employees who work in department 20 or 30, and a second cursor containing the description of departments 20 and 30.

6 libpq C Library

Table of Contents Previous Next