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