libpq C library v17

Suggest edits

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

libpq is also the underlying engine for several other EDB application interfaces, including those written for C++, Perl, Python, Tcl, and ECPG. Some aspects of libpq’s behavior are important to you if you are using one of those packages.

Prerequisites

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

Using libpq with EDB SPL

You can use the EDB SPL language 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, EDB Postgres Advanced Server provided support for REFCURSORs through the following libpq functions. These functions are now deprecated:

PQCursorResult()
PQgetCursorResult()
PQnCursor()

You can 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.

Note

The examples that follow don't include the error-handling code required in a real-world client application.

Returning a single REFCURSOR

This 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 contains one value: the name of the cursor 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:

-- 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 in 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 must not expect a single REFCURSOR in the result set. It must expect two rows, each of which contains 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 returns 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

Binding arrays of parameters

EDB Postgres 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);

You can find details of PQprepare() in the prepared statement section.

You can use the following functions 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

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

You can use this function more than once after PQBulkStart() to send multiple blocks of data.

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 preparing it again.

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 don't need 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 are used repeatedly are parsed and planned just once, rather than each time they're 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);

Using PQBulkStart, PQexecBulk, PQBulkFinish

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

Using PQexecBulkPrepared

This 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

ECPGPlus

↑ Up

Application programmer tools

Connectivity tools

libpq C library v17

Prerequisites

Using libpq with EDB SPL

REFCURSOR support

Note

Returning a single REFCURSOR

Returning multiple REFCURSORs

Binding arrays of parameters

PQBulkStart

API definition

PQexecBulk

API definition

PQBulkFinish

API definition

PQexecBulkPrepared

API definition

Using PQBulkStart, PQexecBulk, PQBulkFinish

Using PQexecBulkPrepared

← Prev

↑ Up

Next →