struct PQconninfoOption { char *keyword; /* The keyword of the option */ char *envvar; /* Fallback environment variable name */ char *compiled; /* Fallback compiled in default value */ char *val; /* Option's current value, or NULL */ char *label; /* Label for field in connect dialog */ char *dispchar; /* Character to display for this field in a connect dialog. Values are: "" Display entered value as is "*" Password field - hide value "D" Debug option - don't show by default */ int dispsize; /* Field size in characters for dialog */ } 返回聯(lián)接選項結(jié)構(gòu)的地址．可以用于獲取所有可能的 PQconnectdb 選項和它們的當(dāng)前缺省值．返回值指向一個 PQconninfoOption 結(jié)構(gòu)數(shù)組，該數(shù)組以一個有 NULL 關(guān)鍵字指針的入口結(jié)束．注意缺省值（"val" 域）將依賴于環(huán)境變量和其他上下文．調(diào)用者必須把聯(lián)接選項當(dāng)作只讀對待． 在處理完選項數(shù)組后，把數(shù)組交給 PQconninfoFree() 釋放．如果沒有這么做，每次調(diào)用 PQconndefaults() 都會有一小部分內(nèi)存泄漏．
int PQntuples(const PGresult *res); PQnfields 返回查詢結(jié)果里每個記錄的數(shù)據(jù)域（字段）的個數(shù)． int PQnfields(const PGresult *res); PQbinaryTuples 如果 PGresult 包含二進(jìn)制記錄數(shù)據(jù)時返回 1，如果包含 ASCII 數(shù)據(jù)返回 0． int PQbinaryTuples(const PGresult *res); 目前，二進(jìn)制記錄數(shù)據(jù)只能從一個從 BINARY 游標(biāo)里抽取數(shù)據(jù)的查詢返回． PQfname 返回與給出的數(shù)據(jù)域索引相關(guān)聯(lián)的數(shù)據(jù)域（字段）的名稱．?dāng)?shù)據(jù)域索引從 0 開始
char *PQfname(const PGresult *res, int field_index); PQfnumber Returns the field (attribute) index associated with the given field name. int PQfnumber(const PGresult *res, const char *field_name); -1 is returned if the given name does not match any field. PQftype 返回與給定數(shù)據(jù)域索引關(guān)聯(lián)的數(shù)據(jù)域類型．整數(shù)返回值是一個該類型的內(nèi)部編碼．?dāng)?shù)據(jù)域索引從 0 開始．
Oid PQftype(const PGresult *res, int field_num); 你可以查詢系統(tǒng)表 pg_type 以獲取各種數(shù)據(jù)類型的名稱和屬性。內(nèi)建的數(shù)據(jù)類型的OID 在源碼樹的 src/include/catalog/pg_type.h 文件里定義?！? PQfsize 返回與給定數(shù)據(jù)域索引關(guān)聯(lián)的數(shù)據(jù)域的大?。?dāng)?shù)據(jù)域索引從 0 開始．
int PQfsize(const PGresult *res, int field_index); PQfsize 返回在數(shù)據(jù)庫記錄里面給該數(shù)據(jù)域分配的空間，換句話說就是該數(shù)據(jù)類型在服務(wù)器里的二進(jìn)制形式的大?。ǔ叽纾绻摂?shù)據(jù)域是可變尺寸，返回 -1． PQfmod 返回與給定數(shù)據(jù)域索引相關(guān)聯(lián)的類型相關(guān)的修正數(shù)據(jù)（??）．?dāng)?shù)據(jù)域索引從 0 開始．
int PQfmod(const PGresult *res, int field_index); PQgetvalue 返回一個 PGresult 里面的一條記錄的單獨(dú)的一個數(shù)據(jù)域（字段）的值．記錄和數(shù)據(jù)域索引從 0 開始． char* PQgetvalue(const PGresult *res, int tup_num, int field_num); 對大多數(shù)查詢而言，PQgetvalue 返回的值是一個表示字段值的空（NULL）結(jié)尾的ASCII 字符串．但是如果 PQbinaryTuples() 為 1，PQgetvalue 返回的值就是該類型在后端服務(wù)器內(nèi)部的二進(jìn)制表現(xiàn)形式（但是不包括尺寸字－－如果數(shù)據(jù)域是變長的）．這樣，把數(shù)據(jù)轉(zhuǎn)換成對應(yīng)的 C 類型就是程序員的責(zé)任了．PQgetvalue 返回的指針指向一個本身是 PGresult 結(jié)構(gòu)的一部分的存儲區(qū)域．我們不能更改它，并且如果我們要在 PGresult 結(jié)構(gòu)的生存期后還要使用它的話，我們必須顯式的把該數(shù)值拷貝到其他存儲器中． PQgetlength 返回以字節(jié)計的數(shù)據(jù)域（字段）的長度．記錄和數(shù)據(jù)域索引從 0 開始．
int PQgetlength(const PGresult *res, int tup_num, int field_num); 這是某一特定數(shù)據(jù)值的實際數(shù)據(jù)長度，也就是由 PQgetvalue 指向的對象的尺寸．注意，對于 ASCII 代表的數(shù)值，這個尺寸與 PQfsize 報告的二進(jìn)制尺寸無關(guān)． PQgetisnull 測試一個數(shù)據(jù)域是否為空（NULL）．記錄和數(shù)據(jù)域索引從 0 開始．
int PQgetisnull(const PGresult *res, int tup_num, int field_num); 如果該域包含 NULL，函數(shù)返回 1，如果包含非空（non-null?。┲担祷亍?．（注意，對一個 NULL 數(shù)據(jù)域，PQgetvalue 將返回一個空字符串，不是一個空指針．） PQcmdStatus 返回產(chǎn)生 PGresult 的 SQL 命令的命令狀態(tài)字符串．
環(huán)境變量 下面的環(huán)境變量可以用于選擇缺省的聯(lián)接參數(shù)值，這些值將被 PQconnectdb 或 PQsetdbLogin 使用－－如果調(diào)用代碼沒有直接聲明相應(yīng)值的話．這些（環(huán)境變量）可以避免把麻煩的數(shù)據(jù)庫名強(qiáng)加入簡單的應(yīng)用程序的硬代碼里面．The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb or PQsetdbLogin if no value is directly specified by the calling code. These are useful to avoid hard-coding database names into simple application programs. PGHOST 設(shè)置缺省的服務(wù)器名．如果聲明了一個非零長的字符串，將使用 TCP/IP 通訊．如果沒有主機(jī)名，libpq 將使用本地的Unix 域套接字． PGPORT 設(shè)置與 Postgres 后端通訊的缺省端口號或本地 Unix 主控套接字的文件擴(kuò)展（文件標(biāo)識符）．
main() { char *pghost, *pgport, *pgoptions, *pgtty; char *dbName; int nFields; int i, j;
/* FILE *debug; */
PGconn *conn; PGresult *res;
/* * begin, by setting the parameters for a backend connection if the * parameters are null, then the system will try to use reasonable * defaults by looking up environment variables or, failing that, * using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend * server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = "template1";
/* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/* * check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to database '%s' failed./n", dbName); fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); }
/* start a transaction block */ res = PQexec(conn, "BEGIN"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "BEGIN command failed/n"); PQclear(res); exit_nicely(conn); }
/* * should PQclear PGresult whenever it is no longer needed to avoid * memory leaks */ PQclear(res);
/* * fetch instances from the pg_database, the system catalog of * databases */ res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from pg_database"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR command failed/n"); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn, "FETCH ALL in mycursor"); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL command didn't return tuples properly/n"); 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 instances */ 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 cursor */ res = PQexec(conn, "CLOSE mycursor"); PQclear(res);
/* commit the transaction */ res = PQexec(conn, "COMMIT"); PQclear(res);
/* close the connection to the database and cleanup */ PQfinish(conn);
/* fclose(debug); */ return 0;
} 例子程序 2 /* * testlibpq2.c * Test of the asynchronous notification interface * * Start this program, then from psql in another window do * NOTIFY TBL2; * * Or, if you want to get fancy, try this: * Populate a database with the following: * * 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 * * INSERT INTO TBL1 values (10); * */ #include #include "libpq-fe.h"
main() { char *pghost, *pgport, *pgoptions, *pgtty; char *dbName; int nFields; int i, j;
PGconn *conn; PGresult *res; PGnotify *notify;
/* * begin, by setting the parameters for a backend connection if the * parameters are null, then the system will try to use reasonable * defaults by looking up environment variables or, failing that, * using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend * server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = getenv("USER"); /* change this to the name of your test * database */
/* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/* * check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to database '%s' failed./n", dbName); fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); }
res = PQexec(conn, "LISTEN TBL2"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "LISTEN command failed/n"); PQclear(res); exit_nicely(conn); }
/* * should PQclear PGresult whenever it is no longer needed to avoid * memory leaks */ PQclear(res);
while (1) {
/* * wait a little bit between checks; waiting with select() * would be more efficient. */ sleep(1); /* collect any asynchronous backend messages */ PQconsumeInput(conn); /* check for asynchronous notify messages */ while ((notify = PQnotifies(conn)) != NULL) { fprintf(stderr, "ASYNC NOTIFY of '%s' from backend pid '%d' received/n", notify->relname, notify->be_pid); free(notify); } }
/* close the connection to the database and cleanup */ PQfinish(conn);
return 0; } 例子程序 3 /* * testlibpq3.c Test the C version of Libpq, the Postgres frontend * library. tests the binary cursor interface * * * * populate a database by doing the following: * * CREATE TABLE test1 (i int4, d float4, p polygon); * * INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, * 2.0)'::polygon); * * INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, * 1.0)'::polygon); * * the expected output is: * * tuple 0: got i = (4 bytes) 1, d = (4 bytes) 3.567000, p = (4 * bytes) 2 points boundbox = (hi=3.000000/4.000000, lo = * 1.000000,2.000000) tuple 1: got i = (4 bytes) 2, d = (4 bytes) * 89.050003, p = (4 bytes) 2 points boundbox = * (hi=4.000000/3.000000, lo = 2.000000,1.000000) * * */ #include #include "libpq-fe.h" #include "utils/geo-decls.h" /* for the POLYGON type */
main() { char *pghost, *pgport, *pgoptions, *pgtty; char *dbName; int nFields; int i, j; int i_fnum, d_fnum, p_fnum; PGconn *conn; PGresult *res;
/* * begin, by setting the parameters for a backend connection if the * parameters are null, then the system will try to use reasonable * defaults by looking up environment variables or, failing that, * using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend * server */ pgtty = NULL; /* debugging tty for the backend server */
dbName = getenv("USER"); /* change this to the name of your test * database */
/* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
/* * check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to database '%s' failed./n", dbName); fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); }
/* start a transaction block */ res = PQexec(conn, "BEGIN"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "BEGIN command failed/n"); PQclear(res); exit_nicely(conn); }
/* * should PQclear PGresult whenever it is no longer needed to avoid * memory leaks */ PQclear(res);
/* * fetch instances from the pg_database, the system catalog of * databases */ res = PQexec(conn, "DECLARE mycursor BINARY CURSOR FOR select * from test1"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR command failed/n"); PQclear(res); exit_nicely(conn); } PQclear(res);
res = PQexec(conn, "FETCH ALL in mycursor"); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL command didn't return tuples properly/n"); PQclear(res); exit_nicely(conn); }
for (i = 0; i < 3; i++) { printf("type[%d] = %d, size[%d] = %d/n", i, PQftype(res, i), i, PQfsize(res, i)); } for (i = 0; i < PQntuples(res); i++) { int *ival; float *dval; int plen; POLYGON *pval;
/* we hard-wire this to the 3 fields we know about */ ival = (int *) PQgetvalue(res, i, i_fnum); dval = (float *) PQgetvalue(res, i, d_fnum); plen = PQgetlength(res, i, p_fnum);
/* * plen doesn't include the length field so need to * increment by VARHDSZ */ pval = (POLYGON *) malloc(plen + VARHDRSZ); pval->size = plen; memmove((char *) &pval->npts, PQgetvalue(res, i, p_fnum), plen); printf("tuple %d: got/n", i); printf(" i = (%d bytes) %d,/n", PQgetlength(res, i, i_fnum), *ival); printf(" d = (%d bytes) %f,/n", PQgetlength(res, i, d_fnum), *dval); printf(" p = (%d bytes) %d points /tboundbox = (hi=%f/%f, lo = %f,%f)/n", PQgetlength(res, i, d_fnum), pval->npts, pval->boundbox.xh, pval->boundbox.yh, pval->boundbox.xl, pval->boundbox.yl); } PQclear(res);
/* close the cursor */ res = PQexec(conn, "CLOSE mycursor"); PQclear(res);
/* commit the transaction */ res = PQexec(conn, "COMMIT"); PQclear(res);
/* close the connection to the database and cleanup */ PQfinish(conn);
