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 */ } 返回联接选项结构的地址.可以用于获取所有可能的 PQconnectdb 选项和它们的当前缺省值.返回值指向一个 PQconninfoOption 结构数组,该数组以一个有 NULL 关键字指针的入口结束.注意缺省值("val" 域)将依赖于环境变量和其他上下文.调用者必须把联接选项当作只读对待. 在处理完选项数组后,把数组交给 PQconninfoFree() 释放.如果没有这么做,每次调用 PQconndefaults() 都会有一小部分内存泄漏.
int PQntuples(const PGresult *res); PQnfields 返回查询结果里每个记录的数据域(字段)的个数. int PQnfields(const PGresult *res); PQbinaryTuples 如果 PGresult 包含二进制记录数据时返回 1,如果包含 ASCII 数据返回 0. int PQbinaryTuples(const PGresult *res); 目前,二进制记录数据只能从一个从 BINARY 游标里抽取数据的查询返回. PQfname 返回与给出的数据域索引相关联的数据域(字段)的名称.数据域索引从 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 返回与给定数据域索引关联的数据域类型.整数返回值是一个该类型的内部编码.数据域索引从 0 开始.
Oid PQftype(const PGresult *res, int field_num); 你可以查询系统表 pg_type 以获取各种数据类型的名称和属性。内建的数据类型的OID 在源码树的 src/include/catalog/pg_type.h 文件里定义。 PQfsize 返回与给定数据域索引关联的数据域的大小.数据域索引从 0 开始.
int PQfsize(const PGresult *res, int field_index); PQfsize 返回在数据库记录里面给该数据域分配的空间,换句话说就是该数据类型在服务器里的二进制形式的大小(尺寸).如果该数据域是可变尺寸,返回 -1. PQfmod 返回与给定数据域索引相关联的类型相关的修正数据(??).数据域索引从 0 开始.
int PQfmod(const PGresult *res, int field_index); PQgetvalue 返回一个 PGresult 里面的一条记录的单独的一个数据域(字段)的值.记录和数据域索引从 0 开始. char* PQgetvalue(const PGresult *res, int tup_num, int field_num); 对大多数查询而言,PQgetvalue 返回的值是一个表示字段值的空(NULL)结尾的ASCII 字符串.但是如果 PQbinaryTuples() 为 1,PQgetvalue 返回的值就是该类型在后端服务器内部的二进制表现形式(但是不包括尺寸字--如果数据域是变长的).这样,把数据转换成对应的 C 类型就是程序员的责任了.PQgetvalue 返回的指针指向一个本身是 PGresult 结构的一部分的存储区域.我们不能更改它,并且如果我们要在 PGresult 结构的生存期后还要使用它的话,我们必须显式的把该数值拷贝到其他存储器中. PQgetlength 返回以字节计的数据域(字段)的长度.记录和数据域索引从 0 开始.
int PQgetlength(const PGresult *res, int tup_num, int field_num); 这是某一特定数据值的实际数据长度,也就是由 PQgetvalue 指向的对象的尺寸.注意,对于 ASCII 代表的数值,这个尺寸与 PQfsize 报告的二进制尺寸无关. PQgetisnull 测试一个数据域是否为空(NULL).记录和数据域索引从 0 开始.
int PQgetisnull(const PGresult *res, int tup_num, int field_num); 如果该域包含 NULL,函数返回 1,如果包含非空(non-null )值,返回 0.(注意,对一个 NULL 数据域,PQgetvalue 将返回一个空字符串,不是一个空指针.) PQcmdStatus 返回产生 PGresult 的 SQL 命令的命令状态字符串.
环境变量 下面的环境变量可以用于选择缺省的联接参数值,这些值将被 PQconnectdb 或 PQsetdbLogin 使用--如果调用代码没有直接声明相应值的话.这些(环境变量)可以避免把麻烦的数据库名强加入简单的应用程序的硬代码里面.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 设置缺省的服务器名.如果声明了一个非零长的字符串,将使用 TCP/IP 通讯.如果没有主机名,libpq 将使用本地的Unix 域套接字. PGPORT 设置与 Postgres 后端通讯的缺省端口号或本地 Unix 主控套接字的文件扩展(文件标识符).
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);