本文首发于 2015-11-12 09:23:46

前言

本文成文较早，测试程序验证的是 PostgreSQL 9.3 版本的 libpq，实践上，对其余版本也应该实用。

对于 libpq 各个函数接口的阐明，参考：http://www.postgres.cn/docs/9...

示例1

/* * testlibpq.c * *      Test the C version of libpq, the PostgreSQL frontend library. */#include <stdio.h>#include <stdlib.h>#include <libpq-fe.h> static voidexit_nicely(PGconn *conn){    PQfinish(conn);    exit(1);} intmain(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;}

示例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 voidexit_nicely(PGconn *conn){    PQfinish(conn);    exit(1);} intmain(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;}

示例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 voidexit_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 voidshow_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");    }} intmain(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",                       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;}

欢送关注我的微信公众号【数据库内核】：分享支流开源数据库和存储引擎相干技术。