libpq C Library v11

Suggest edits

libpq is the C application programmer’s interface to Advanced Server. libpq is a set of library functions that allow client programs to pass queries to the Advanced Server and to receive the results of these queries.

libpq is also the underlying engine for several other EnterpriseDB application interfaces including those written for C++, Perl, Python, Tcl and ECPG. So some aspects of libpq’s behavior will be important to the user if one of those packages is used.

Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library.

Using libpq with EnterpriseDB SPL

The EnterpriseDB SPL language can be used with the libpq interface library, providing support for:

Procedures, functions, packages
Prepared statements
REFCURSORs
Static cursors
structs and typedefs
Arrays
DML and DDL operations
IN/OUT/IN OUT parameters

REFCURSOR Support

In earlier releases, Advanced Server provided support for REFCURSORs through the following libpq functions; these functions should now be considered deprecated:

PQCursorResult()
PQgetCursorResult()
PQnCursor()

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.

Please note that the samples that follow do not include error-handling code that would be required in a real-world client application.

Returning a Single REFCURSOR

The following example shows an SPL function that returns a value of type REFCURSOR:

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.

To call this function from a C client using libpq, you can use PQexec() and PQgetvalue():

#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 getEmployees(10)");

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

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

  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);
}

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:

result = PQexec(conn, "SELECT * FROM getEmployees(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 output of this program is shown below:

-- employees --
    7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
    7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
    7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

Returning Multiple REFCURSORs

The next example returns two REFCURSORs:

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).

CREATE OR REPLACE FUNCTION getEmpsAndDepts(p_min NUMERIC,
                                           p_max NUMERIC)
RETURN SETOF REFCURSOR AS
  employees   REFCURSOR;
  departments REFCURSOR;
BEGIN
  OPEN employees FOR
    SELECT * FROM emp WHERE deptno BETWEEN p_min AND p_max;
  RETURN NEXT employees;

  OPEN departments FOR
    SELECT * FROM dept WHERE deptno BETWEEN p_min AND p_max;
  RETURN NEXT departments;
END;

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.

-- employees --
  7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
  7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
  7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
  7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
  7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
  7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
  7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
  7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
  7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
  7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
  7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
-- departments --
  20,RESEARCH,DALLAS
  30,SALES,CHICAGO

Array Binding

Advanced Server's array binding functionality allows you to send an array of data across the network in a single round-trip. When the back end receives the bulk data, it can use the data to perform insert or update operations.

Perform bulk operations with a prepared statement; use the following function to prepare the statement:

PGresult *PQprepare(PGconn *conn,
                    const char *stmtName,
                    const char *query,
                    int nParams,
                    const Oid *paramTypes);

Details of PQprepare() can be found in the prepared statement section.

The following functions can be used to perform bulk operations:

PQBulkStart
PQexecBulk
PQBulkFinish
PQexecBulkPrepared

PQBulkStart

PQBulkStart() initializes bulk operations on the server. You must call this function before sending bulk data to the server. PQBulkStart() initializes the prepared statement specified in stmtName to receive data in a format specified by paramFmts.

API Definition

PGresult *PQBulkStart(PGconn *conn,
                      const char *Stmt_Name,
                      unsigned int nCol,
                      const int *paramFmts);

PQexecBulk

PQexecBulk() is used to supply data (paramValues) for a statement that was previously initialized for bulk operation using PQBulkStart().

This function can be used more than once after PQBulkStart() to send multiple blocks of data. See the example for more details.

API Definition

PGresult *PQexecBulk(PGconn *conn,
                     unsigned int nRows,
                     const char *const * paramValues,
                     const int *paramLengths);

PQBulkFinish

This function completes the current bulk operation. You can use the prepared statement again without re-preparing it.

API Definition

PGresult *PQBulkFinish(PGconn *conn);

PQexecBulkPrepared

Alternatively, you can use the PQexecBulkPrepared() function to perform a bulk operation with a single function call. PQexecBulkPrepared() sends a request to execute a prepared statement with the given parameters, and waits for the result. This function combines the functionality of PQbulkStart(), PQexecBulk(), and PQBulkFinish(). When using this function, you are not required to initialize or terminate the bulk operation; this function starts the bulk operation, passes the data to the server, and terminates the bulk operation.

Specify a previously prepared statement in the place of stmtName. Commands that will be used repeatedly will be parsed and planned just once, rather than each time they are executed.

API Definition

PGresult *PQexecBulkPrepared(PGconn *conn,
                             const char *stmtName,
                             unsigned int nCols,
                             unsigned int nRows,
                             const char *const *paramValues,
                             const int *paramLengths,
                             const int *paramFormats);

Example Code (Using PQBulkStart, PQexecBulk, PQBulkFinish)

The following example uses PGBulkStart, PQexecBulk, and PQBulkFinish.

void InsertDataUsingBulkStyle( PGconn *conn )
{
    PGresult            *res;
    Oid                 paramTypes[2];
    char                *paramVals[5][2];
    int                 paramLens[5][2];
    int                 paramFmts[2];
    int                 i;

    int                 a[5] = { 10, 20, 30, 40, 50 };
    char                b[5][10] = { "Test_1", "Test_2", "Test_3",
"Test_4", "Test_5" };


    paramTypes[0] = 23;
    paramTypes[1] = 1043;
    res = PQprepare( conn, "stmt_1", "INSERT INTO testtable1 values( $1, $2
)", 2, paramTypes );
    PQclear( res );

    paramFmts[0] = 1;   /* Binary format */
    paramFmts[1] = 0;

    for( i = 0; i < 5; i++ )
    {
        a[i] = htonl( a[i] );
        paramVals[i][0] = &(a[i]);
        paramVals[i][1] = b[i];

        paramLens[i][0] = 4;
        paramLens[i][1] = strlen( b[i] );
    }

       res = PQBulkStart(conn, "stmt_1", 2, paramFmts);
       PQclear( res );
       printf( "< -- PQBulkStart -- >\n" );

       res = PQexecBulk(conn, 5, (const char *const *)paramVals, (const
     int*)paramLens);
       PQclear( res );
       printf( "< -- PQexecBulk -- >\n" );

       res = PQexecBulk(conn, 5, (const char *const *)paramVals, (const
     int*)paramLens);
       PQclear( res );
       printf( "< -- PQexecBulk -- >\n" );

       res = PQBulkFinish(conn);
       PQclear( res );
       printf( "< -- PQBulkFinish -- >\n" );
}

Example Code (Using PQexecBulkPrepared)

The following example uses PQexecBulkPrepared.

void InsertDataUsingBulkStyleCombinedVersion( PGconn *conn )
{
       PGresult            *res;
       Oid                 paramTypes[2];
       char                *paramVals[5][2];
       int                 paramLens[5][2];
       int                 paramFmts[2];
       int                 i;

       int                 a[5] = { 10, 20, 30, 40, 50 };
       char                b[5][10] = { "Test_1", "Test_2", "Test_3",
"Test_4", "Test_5" };

       paramTypes[0] = 23;
       paramTypes[1] = 1043;
       res = PQprepare( conn, "stmt_2", "INSERT INTO testtable1 values(
     $1, $2)", 2, paramTypes );
       PQclear( res );

       paramFmts[0] = 1;   /* Binary format */
       paramFmts[1] = 0;

       for( i = 0; i < 5; i++ )
       {
                a[i] = htonl( a[i] );
                paramVals[i][0] = &(a[i]);
                paramVals[i][1] = b[i];

                paramLens[i][0] = 4;
                paramLens[i][1] = strlen( b[i] );
       }

res = PQexecBulkPrepared(conn, "stmt_2", 2, 5, (const char *const
*)paramVals,(const int *)paramLens, (const int *)paramFmts);
       PQclear( res );
}

← Prev

EDB Resource Manager

Debugger

libpq C Library v11

Using libpq with EnterpriseDB SPL

REFCURSOR Support

Array Binding

PQBulkStart

PQexecBulk

PQBulkFinish

PQexecBulkPrepared

Example Code (Using PQBulkStart, PQexecBulk, PQBulkFinish)

Example Code (Using PQexecBulkPrepared)

← Prev

Next →