32.22. Example Programs (original) (raw)

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

Example 32.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, "%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 32.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/select.h> #include <sys/time.h> #include <sys/types.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, "%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 32.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;
SET standard_conforming_strings = ON;
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, "%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;

}