## 27.16. Example Programs

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

Example 27.1. libpq Example Program 1

    /*
     * testlibpq.c
     *
     *              Test the C version of LIBPQ, the POSTGRES 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=template1
             * and using environment variables or defaults for all other connection
             * parameters.
             */
            if (argc > 1)
                    conninfo = argv[1];
            else
                    conninfo = "dbname = template1";

            /* 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;
    }

Example 27.2. 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=template1
             * and using environment variables or defaults for all other connection
             * parameters.
             */
            if (argc > 1)
                    conninfo = argv[1];
            else
                    conninfo = "dbname = template1";

            /* 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;
    }

Example 27.3. 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
     *
     */
    #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);
    }

    int
    main(int argc, char **argv)
    {
            const char *conninfo;
            PGconn     *conn;
            PGresult   *res;
            const char *paramValues[1];
            int                     i,
                                    j;
            int                     i_fnum,
                                    t_fnum,
                                    b_fnum;

            /*
             * If the user supplies a parameter on the command line, use it as
             * the conninfo string; otherwise default to setting dbname=template1
             * and using environment variables or defaults for all other connection
             * parameters.
             */
            if (argc > 1)
                    conninfo = argv[1];
            else
                    conninfo = "dbname = template1";

            /* 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
             * results.  By using out-of-line parameters we can avoid a lot of
             * tedious mucking about with quoting and escaping.  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",
                                               1,           /* one param */
                                               NULL,        /* let the backend deduce param type */
                                               paramValues,
                                               NULL,        /* don't need param lengths since text */
                                               NULL,        /* default to all text params */
                                               1);          /* ask for binary results */

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

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

            PQclear(res);

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

            return 0;
    }