## 34.21. Example Programs

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

Example 34.1. libpq Example Program 1

    /*
     * src/test/examples/testlibpq.c
     *
     *
     * 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);
        }

        /* Set always-secure search path, so malicious users can't take control. */
        res = PQexec(conn,
                     "SELECT pg_catalog.set_config('search_path', '', false)");
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
            PQclear(res);
            exit_nicely(conn);
        }

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

        /*
         * 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);
        }
        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 34.2. libpq Example Program 2

    /*
     * src/test/examples/testlibpq2.c
     *
     *
     * 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 SCHEMA TESTLIBPQ2;
     *   SET search_path = TESTLIBPQ2;
     *   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);
     *
     * Start this program, then from psql do this four times:
     *
     *   INSERT INTO TESTLIBPQ2.TBL1 VALUES (10);
     */

    #ifdef WIN32
    #include <windows.h>
    #endif
    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>
    #include <errno.h>
    #include <sys/time.h>
    #include <sys/types.h>
    #ifdef HAVE_SYS_SELECT_H
    #include <sys/select.h>
    #endif

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

        /* Set always-secure search path, so malicious users can't take control. */
        res = PQexec(conn,
                     "SELECT pg_catalog.set_config('search_path', '', false)");
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
            PQclear(res);
            exit_nicely(conn);
        }

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

        /*
         * 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);
        }
        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++;
                PQconsumeInput(conn);
            }
        }

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

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

        return 0;
    }

Example 34.3. libpq Example Program 3

    /*
     * src/test/examples/testlibpq3.c
     *
     *
     * 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 SCHEMA testlibpq3;
     * SET search_path = testlibpq3;
     * 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
     */

    #ifdef WIN32
    #include <windows.h>
    #endif

    #include <stdio.h>
    #include <stdlib.h>
    #include <stdint.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);
        }

        /* Set always-secure search path, so malicious users can't take control. */
        res = PQexec(conn, "SET search_path = testlibpq3");
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
            PQclear(res);
            exit_nicely(conn);
        }
        PQclear(res);

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

        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",
                           1,       /* one param */
                           NULL,    /* let the backend deduce param type */
                           paramValues,
                           paramLengths,
                           paramFormats,
                           1);      /* ask for binary results */

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