# 31.21\. 例子程序 这些例子和其他的可以在字典`src/test/examples`的源代码分布中找到。 **Example 31-1\. libpq 例子程序 1** ``` /* * testlibpq.c * * <!-- Test the C version of libpq, the PostgreSQL frontend library. -->测试libpq的C版本，PostgreSQL前端库。 */ #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. --> * 如果用户在命令行上提供了一个参数，则拿它当作 conninfo 字串使用； * 否则缺省为 dbname=postgres 并且使用环境变量或者所有其它连接参数 * 都使用缺省值。 */ 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. --> * 我们的测试实例涉及游标的使用，这个时候我们必须使用事务块。 * 我们可以把全部事情放在一个 "select * from pg_database" * PQexec() 里，不过那样太简单了，不是个好例子。 */ /* <!-- 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 PGresult，以避免内存泄漏 */ PQclear(res); /* <!-- Fetch rows from pg_database, the system catalog of databases --> * 从系统表 pg_database（数据库的系统目录）里抓取数据 */ 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 31-2\. libpq 例子程序 2** ``` /* * testlibpq2.c * <!-- Test of the asynchronous notification interface -->测试异步通知接口 * * <!-- Start this program, then from psql in another window do -->运行此程序，然后从另外一个窗口的 psql 里运行 * 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): --> * 或者，如果你想好玩一点，用下面命令填充数据库： * （在 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. --> * 如果用户在命令行上提供了参数， * 那么拿它当作 conninfo 字串；否则缺省设置是 dbname=postgres * 并且对其它连接使用环境变量或者缺省值。 */ 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. --> * 发出 LISTEN 命令打开来自规则 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 PGresult ，以避免内存泄漏 */ 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. --> * 睡眠，直到某些事件发生。我们使用 select(2) 等待输入， * 但是也可以用 poll() 或者类似的设施。 */ 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 31-3\. libpq 例子程序 3** ``` /* * testlibpq3.c * <!-- Test out-of-line parameters and binary I/O. -->测试外联参数和二进制I/O。 * <!-- * Before running this, populate a database with the following commands * (provided in src/test/examples/testlibpq3.sql): --> * 在运行这个例子之前，用下面的命令填充一个数据库 * （在 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. --> * 这个函数打印查询结果，这些结果是二进制格式，从上面的 * 注释里面创建的表中抓取出来的。我们把这个函数单独拆出来 * 是因为 main() 函数用了它两次。 */ 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 -->使用 PQfnumber 来避免对结果中的字段顺序进行假设 */ 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. --> * INT4 的二进制表现形式是网络字节序， * 我们最好转换成本地字节序。 */ 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. --> * TEXT 的二进制表现形式是，嗯，文本，因此 libpq 足够给它附加一个字节零， * 因此把它看做 C 字串就挺好。 * * BYTEA 的二进制表现形式是一堆字节，里面可能包含嵌入的空值， * 因此我们必须注意字段长度。 */ 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. --> * 如果用户在命令行上提供了参数， * 那么拿它当作 conninfo 字串；否则缺省设置是 dbname=postgres * 并且对其它连接参数使用环境变量或者缺省值。 */ 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. --> * 这个程序是用来演示使用外联参数的 PQexecParams()， * 以及数据的二进制传输。第一个例子使用文本传输参数， * 但是用二进制格式接收结果。通过使用外联参数，我们可以避免大量 * 枯燥的字串的引用和逃逸，即使数据是文本。请注意我们这里不需要对参数值里的引号 * 做任何特殊的处理。 */ /* <!-- 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. --> * 在这个第二个例子里，我们以二进制格式传输一个整数参数， * 然后还是以二进制格式检索结果。 * * 尽管我们告诉 PQexecParams，我们让后端推导参数类型， * 实际上我们通过在查询字串里转换参数符号的方法强制了决定的做出。 * 在发送二进制参数的时候，这是一个很好的安全检查。 */ /* <!-- Convert integer value "2" to network byte order -->把整数值 "2" 转换成网络字节序 */ binaryIntVal = htonl((uint32_t) 2); /* <!-- Set up parameter arrays for PQexecParams -->为 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; } ```