- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

1.19 Example Programs

These examples and others can be found in the directory ‘src/test/examples’ in the source code distribution.

libpq Example Program 1:

/* testlibpq.c Test the C version of libpq, the PostgreSQL
   frontend library. */
#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;

  /* If the user supplies a parameter on the command line,
     use it as the conninfo string; otherwise default to
     setting dbname=postgres and using environment
     variables or defaults for all other connection
     parameters. */
  if (argc > 1)
    conninfo = argv[1];
  else
    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, "Connection to database failed: %s",
            PQerrorMessage(conn));
    exit_nicely(conn);
  }

  /* Our test case here involves using a cursor, for which
     we must be inside a transaction block.  We could do
     the whole thing with a single PQexec() of "select *
     from pg_database", but that's too trivial to make a
     good example. */

  /* Start a transaction block */
  res = PQexec(conn, "BEGIN");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "BEGIN command failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }

  /* Should PQclear PGresult whenever it is no longer
     needed to avoid memory leaks */
  PQclear(res);

  /* Fetch rows from pg_database, the system catalog of
     databases */
  res =
      PQexec(conn,
             "DECLARE myportal CURSOR FOR select * from
              pg_database");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "DECLARE CURSOR failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }
  PQclear(res);

  res = PQexec(conn, "FETCH ALL in myportal");
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    fprintf(stderr, "FETCH ALL failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }

  /* 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 portal ... we don't bother to check for
     errors ... */
  res = PQexec(conn, "CLOSE myportal");
  PQclear(res);

  /* end the transaction */
  res = PQexec(conn, "END");
  PQclear(res);

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

  return 0;
}

libpq Example Program 2:

/*testlibpq2.c
 *      Test of the asynchronous notification interface
 *
 * Start this program, then from psql in another window do
 *   NOTIFY TBL2;
 * Repeat four times to get this program to exit.
 *
 * Or, if you want to get fancy, try this:
 * populate a database with the following commands
 * (provided in src/test/examples/testlibpq2.sql):
 *
 *   CREATE TABLE TBL1 (i int4);
 *
 *   CREATE TABLE TBL2 (i int4);
 *
 *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
 *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
 *
 * and do this four times:
 *
 *   INSERT INTO TBL1 VALUES (10);
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.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;
  PGnotify *notify;
  int nnotifies;

  /* If the user supplies a parameter on the command line,
     use it as the conninfo string; otherwise default to
     setting dbname=postgres and using environment
     variables or defaults for all other connection
     parameters. */
  if (argc > 1)
    conninfo = argv[1];
  else
    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, "Connection to database failed: %s",
            PQerrorMessage(conn));
    exit_nicely(conn);
  }

  /* Issue LISTEN command to enable notifications from the
     rule's NOTIFY. */
  res = PQexec(conn, "LISTEN TBL2");
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "LISTEN command failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }

  /* should PQclear PGresult whenever it is no longer
     needed to avoid memory leaks */
  PQclear(res);

  /* Quit after four notifies are received. */
  nnotifies = 0;
  while (nnotifies < 4) {
    /* Sleep until something happens on the connection.  We
       use select(2) to wait for input, but you could also
       use poll() or similar facilities. */
    int sock;
    fd_set input_mask;

    sock = PQsocket(conn);

    if (sock < 0)
      break;  /* shouldn't happen */

    FD_ZERO(&input_mask);
    FD_SET(sock, &input_mask);

    if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0) {
      fprintf(stderr, "select() failed: %s\n",
              strerror(errno));
      exit_nicely(conn);
    }

    /* Now check for input */
    PQconsumeInput(conn);
    while ((notify = PQnotifies(conn)) != NULL) {
      fprintf(stderr,
              "ASYNC NOTIFY of '%s' received from
               backend pid %d\n",
              notify->relname, notify->be_pid);
      PQfreemem(notify);
      nnotifies++;
    }
  }

  fprintf(stderr, "Done.\n");

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

  return 0;
}

libpq Example Program 3:

/*testlibpq3.c
 *      Test out-of-line parameters and binary I/O.
 *
 * Before running this, populate a database with the
 following commands
 * (provided in src/test/examples/testlibpq3.sql):
 *
 * CREATE TABLE test1 (i int4, t text, b bytea);
 *
 * INSERT INTO test1 values (1, 'joe”s place',
 '\\000\\001\\002\\003\\004');
 * INSERT INTO test1 values (2, 'ho there',
 '\\004\\003\\002\\001\\000');
 *
 * The expected output is:
 *
 * tuple 0: got
 *  i = (4 bytes) 1
 *  t = (11 bytes) 'joe's place'
 *  b = (5 bytes) \000\001\002\003\004
 *
 * tuple 0: got
 *  i = (4 bytes) 2
 *  t = (8 bytes) 'ho there'
 *  b = (5 bytes) \004\003\002\001\000
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>

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

/* This function prints a query result that is a
   binary-format fetch from a table defined as in the
   comment above.  We split it out because the main()
   function uses it twice. */
static void
show_binary_results(PGresult * res)
{
  int i, j;
  int i_fnum, t_fnum, b_fnum;

  /* Use PQfnumber to avoid assumptions about field order
     in result */
  i_fnum = PQfnumber(res, "i");
  t_fnum = PQfnumber(res, "t");
  b_fnum = PQfnumber(res, "b");

  for (i = 0; i < PQntuples(res); i++) {
    char *iptr;
    char *tptr;
    char *bptr;
    int blen;
    int ival;

    /* Get the field values (we ignore possibility they are
       null!) */
    iptr = PQgetvalue(res, i, i_fnum);
    tptr = PQgetvalue(res, i, t_fnum);
    bptr = PQgetvalue(res, i, b_fnum);

    /* The binary representation of INT4 is in network byte
       order, which we'd better coerce to the local byte
       order. */
    ival = ntohl(*((uint32_t *) iptr));

    /* The binary representation of TEXT is, well, text,
       and since libpq was nice enough to append a zero
       byte to it, it'll work just fine as a C string. The
       binary representation of BYTEA is a bunch of bytes,
       which could include embedded nulls so we have to pay
       attention to field length. */
    blen = PQgetlength(res, i, b_fnum);

    printf("tuple %d: got\n", i);
    printf(" i = (%d bytes) %d\n",
           PQgetlength(res, i, i_fnum), ival);
    printf(" t = (%d bytes) '%s'\n",
           PQgetlength(res, i, t_fnum), tptr);
    printf(" b = (%d bytes) ", blen);
    for (j = 0; j < blen; j++)
      printf("\\%03o", bptr[j]);
    printf("\n\n");
  }
}

int
main(int argc, char **argv)
{
  const char *conninfo;
  PGconn *conn;
  PGresult *res;
  const char *paramValues[1];
  int paramLengths[1];
  int paramFormats[1];
  uint32_t binaryIntVal;

  /* If the user supplies a parameter on the command line,
     use it as the conninfo string; otherwise default to
     setting dbname=postgres and using environment
     variables or defaults for all other connection
     parameters. */
  if (argc > 1)
    conninfo = argv[1];
  else
    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, "Connection to database failed: %s",
            PQerrorMessage(conn));
    exit_nicely(conn);
  }

  /* The point of this program is to illustrate use of
     PQexecParams() with out-of-line parameters, as well as
     binary transmission of data. This first example
     transmits the parameters as text, but receives the
     results in binary format.  By using out-of-line
     parameters we can avoid a lot of tedious mucking about
     with quoting and escaping, even though the data is
     text.  Notice how we don't have to do anything special
     with the quote mark in the parameter value. */

  /* Here is our out-of-line parameter value */
  paramValues[0] = "joe's place";

  res =
      PQexecParams(conn, "SELECT * FROM test1 WHERE t = $1",
                   /* one param */
                   1,
                   /* let the backend deduce param type */
                   NULL, paramValues, NULL, /* don't need
                                               param
                                               lengths
                                               since text */
                   NULL,  /* default to all text params */
                   /* ask for binary results */
                   1);

  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    fprintf(stderr, "SELECT failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }

  show_binary_results(res);

  PQclear(res);

  /* In this second example we transmit an integer
     parameter in binary form, and again retrieve the
     results in binary form. Although we tell PQexecParams
     we are letting the backend deduce parameter type, we
     really force the decision by casting the parameter
     symbol in the query text.  This is a good safety
     measure when sending binary parameters. */

  /* Convert integer value "2" to network byte order */
  binaryIntVal = htonl((uint32_t) 2);

  /* Set up parameter arrays for PQexecParams */
  paramValues[0] = (char *) &binaryIntVal;
  paramLengths[0] = sizeof(binaryIntVal);
  paramFormats[0] = 1;  /* binary */

  res =
      PQexecParams(conn,
                   "SELECT * FROM test1 WHERE i =
                    $1::int4",
                   /* one param */
                   1,
                   /* let the backend deduce param type */
                   NULL, paramValues, paramLengths,
                   paramFormats,
                   /* ask for binary results */
                   1);

  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    fprintf(stderr, "SELECT failed: %s",
            PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
  }

  show_binary_results(res);

  PQclear(res);

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

  return 0;
}
ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition