示例

常用功能示例代码

示例1：

1. /*
2. * testlibpq.c
3. */
4. #include <stdio.h>
5. #include <stdlib.h>
6. #include <libpq-fe.h>

8. static void
9. exit_nicely(PGconn *conn)
10. {
11. PQfinish(conn);
12. exit(1);
13. }

15. int
16. main(int argc, char **argv)
17. {
18. const char *conninfo;
19. PGconn     *conn;
20. PGresult   *res;
21. int         nFields;
22. int         i,j;

24. /*
25. * 用户在命令行上提供了conninfo字符串的值时使用该值
26. * 否则环境变量或者所有其它连接参数
27. * 都使用缺省值。
28. */
29. if (argc > 1)
30. conninfo = argv[1];
31. else
32. conninfo = "dbname=postgres port=42121 host='10.44.133.171' application_name=test connect_timeout=5 sslmode=allow user='test' password='xxxxxx'";

34. /* 连接数据库 */
35. conn = PQconnectdb(conninfo);

37. /* 检查后端连接成功建立 */
38. if (PQstatus(conn) != CONNECTION_OK)
39. {
40. fprintf(stderr, "Connection to database failed: %s",
41. PQerrorMessage(conn));
42. exit_nicely(conn);
43. }

45. /*
46. * 测试实例涉及游标的使用时候必须使用事务块
47. *把全部放在一个  "select * from pg_database"
48. * PQexec() 里，过于简单，不推荐使用
49. */

51. /* 开始一个事务块 */
52. res = PQexec(conn, "BEGIN");
53. if (PQresultStatus(res) != PGRES_COMMAND_OK)
54. {
55. fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
56. PQclear(res);
57. exit_nicely(conn);
58. }

60. /*
61. * 在结果不需要的时候PQclear PGresult，以避免内存泄漏
62. */
63. PQclear(res);

65. /*
66. * 从系统表 pg_database（数据库的系统目录）里抓取数据
67. */
68. res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
69. if (PQresultStatus(res) != PGRES_COMMAND_OK)
70. {
71. fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
72. PQclear(res);
73. exit_nicely(conn);
74. }
75. PQclear(res);

77. res = PQexec(conn, "FETCH ALL in myportal");
78. if (PQresultStatus(res) != PGRES_TUPLES_OK)
79. {
80. fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
81. PQclear(res);
82. exit_nicely(conn);
83. }

85. /* 打印属性名称 */
86. nFields = PQnfields(res);
87. for (i = 0; i < nFields; i++)
88. printf("%-15s", PQfname(res, i));
89. printf("\n\n");

91. /* 打印行 */
92. for (i = 0; i < PQntuples(res); i++)
93. {
94. for (j = 0; j < nFields; j++)
95. printf("%-15s", PQgetvalue(res, i, j));
96. printf("\n");
97. }

99. PQclear(res);

101. /* 关闭入口 ... 不用检查错误 ... */
102. res = PQexec(conn, "CLOSE myportal");
103. PQclear(res);

105. /* 结束事务 */
106. res = PQexec(conn, "END");
107. PQclear(res);

109. /* 关闭数据库连接并清理 */
110. PQfinish(conn);

112. return 0;
113. }

示例2：

1. /*
2. * testlibpq2.c
3. *      测试外联参数和二进制I/O。
4. *
5. * 在运行这个例子之前，用下面的命令填充一个数据库
6. *
7. *
8. * CREATE TABLE test1 (i int4, t text);
9. *
10. * INSERT INTO test1 values (2, 'ho there');
11. *
12. * 期望的输出如下
13. *
14. *
15. * tuple 0: got
16. *  i = (4 bytes) 2
17. *  t = (8 bytes) 'ho there'
18. *
19. */
20. #include <stdio.h>
21. #include <stdlib.h>
22. #include <string.h>
23. #include <sys/types.h>
24. #include <libpq-fe.h>

26. /* for ntohl/htonl */
27. #include <netinet/in.h>
28. #include <arpa/inet.h>

30. static void
31. exit_nicely(PGconn *conn)
32. {
33. PQfinish(conn);
34. exit(1);
35. }

37. /*
38. * 这个函数打印查询结果，这些结果是二进制格式，从上面的
39. * 注释里面创建的表中抓取出来的
40. */
41. static void
42. show_binary_results(PGresult *res)
43. {
44. int         i;
45. int         i_fnum,
46. t_fnum;

48. /* 使用 PQfnumber 来避免对结果中的字段顺序进行假设 */
49. i_fnum = PQfnumber(res, "i");
50. t_fnum = PQfnumber(res, "t");

52. for (i = 0; i < PQntuples(res); i++)
53. {
54. char       *iptr;
55. char       *tptr;
56. int         ival;

58. /* 获取字段值（忽略可能为空的可能） */
59. iptr = PQgetvalue(res, i, i_fnum);
60. tptr = PQgetvalue(res, i, t_fnum);

62. /*
63. * INT4 的二进制表现形式是网络字节序
64. * 建议转换成本地字节序
65. */
66. ival = ntohl(*((uint32_t *) iptr));

68. /*
69. * TEXT 的二进制表现形式是文本，因此libpq能够给它附加一个字节零
70. * 把它看做 C 字符串
71. *
72. */

74. printf("tuple %d: got\n", i);
75. printf(" i = (%d bytes) %d\n",
76. PQgetlength(res, i, i_fnum), ival);
77. printf(" t = (%d bytes) '%s'\n",
78. PQgetlength(res, i, t_fnum), tptr);
79. printf("\n\n");
80. }
81. }

83. int
84. main(int argc, char **argv)
85. {
86. const char *conninfo;
87. PGconn     *conn;
88. PGresult   *res;
89. const char *paramValues[1];
90. int         paramLengths[1];
91. int         paramFormats[1];
92. uint32_t    binaryIntVal;

94. /*
95. * 如果用户在命令行上提供了参数，
96. * 那么使用该值为conninfo 字符串；否则
97. * 使用环境变量或者缺省值。
98. */
99. if (argc > 1)
100. conninfo = argv[1];
101. else
102. conninfo = "dbname=postgres port=42121 host='10.44.133.171' application_name=test connect_timeout=5 sslmode=allow user='test' password='xxxxxx'";

104. /* 和数据库建立连接 */
105. conn = PQconnectdb(conninfo);

107. /* 检查与服务器的连接是否成功建立 */
108. if (PQstatus(conn) != CONNECTION_OK)
109. {
110. fprintf(stderr, "Connection to database failed: %s",
111. PQerrorMessage(conn));
112. exit_nicely(conn);
113. }

115. /* 把整数值 "2" 转换成网络字节序 */
116. binaryIntVal = htonl((uint32_t) 2);

118. /* 为 PQexecParams 设置参数数组 */
119. paramValues[0] = (char *) &binaryIntVal;
120. paramLengths[0] = sizeof(binaryIntVal);
121. paramFormats[0] = 1;        /* 二进制 */

123. res = PQexecParams(conn,
124. "SELECT * FROM test1 WHERE i = $1::int4",
125. 1,       /* 一个参数 */
126. NULL,    /* 让后端推导参数类型 */
127. paramValues,
128. paramLengths,
129. paramFormats,
130. 1);      /* 要求二进制结果 */

132. if (PQresultStatus(res) != PGRES_TUPLES_OK)
133. {
134. fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
135. PQclear(res);
136. exit_nicely(conn);
137. }

139. show_binary_results(res);

141. PQclear(res);

143. /* 关闭与数据库的连接并清理 */
144. PQfinish(conn);

146. return 0;
147. }

示例 3:

1. /*-----------------------------------------------------------------
2. *
3. * testlo.c
4. *    test using large objects with libpq
5. *
6. * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
7. * Portions Copyright (c) 1994, Regents of the University of California
8. *
9. *
10. * IDENTIFICATION
11. *    src/test/examples/testlo.c
12. *
13. *-----------------------------------------------------------------
14. */
15. #include <stdio.h>
16. #include <stdlib.h>

18. #include <sys/types.h>
19. #include <sys/stat.h>
20. #include <fcntl.h>
21. #include <unistd.h>

23. #include "libpq-fe.h"
24. #include "libpq/libpq-fs.h"

26. #define BUFSIZE         1024

28. /*
29. * importFile -
30. *    import file "in_filename" into database as large object "lobjOid"
31. *
32. */
33. static Oid
34. importFile(PGconn *conn, char *filename)
35. {
36. Oid         lobjId;
37. int         lobj_fd;
38. char        buf[BUFSIZE];
39. int         nbytes,
40. tmp;
41. int         fd;

43. /*
44. * open the file to be read in
45. */
46. fd = open(filename, O_RDONLY, 0666);
47. if (fd < 0)
48. {                           /* error */
49. fprintf(stderr, "cannot open unix file\"%s\"\n", filename);
50. }

52. /*
53. * create the large object
54. */
55. lobjId = lo_creat(conn, INV_READ | INV_WRITE);
56. if (lobjId == 0)
57. fprintf(stderr, "cannot create large object");

59. lobj_fd = lo_open(conn, lobjId, INV_WRITE);

61. /*
62. * read in from the Unix file and write to the inversion file
63. */
64. while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
65. {
66. tmp = lo_write(conn, lobj_fd, buf, nbytes);
67. if (tmp < nbytes)
68. fprintf(stderr, "error while reading \"%s\"", filename);
69. }

71. close(fd);
72. lo_close(conn, lobj_fd);

74. return lobjId;
75. }

77. static void
78. pickout(PGconn *conn, Oid lobjId, int start, int len)
79. {
80. int         lobj_fd;
81. char       *buf;
82. int         nbytes;
83. int         nread;

85. lobj_fd = lo_open(conn, lobjId, INV_READ);
86. if (lobj_fd < 0)
87. fprintf(stderr, "cannot open large object %u", lobjId);

89. lo_lseek(conn, lobj_fd, start, SEEK_SET);
90. buf = malloc(len + 1);

92. nread = 0;
93. while (len - nread > 0)
94. {
95. nbytes = lo_read(conn, lobj_fd, buf, len - nread);
96. buf[nbytes] = '\0';
97. fprintf(stderr, ">>> %s", buf);
98. nread += nbytes;
99. if (nbytes <= 0)
100. break;              /* no more data? */
101. }
102. free(buf);
103. fprintf(stderr, "\n");
104. lo_close(conn, lobj_fd);
105. }

107. static void
108. overwrite(PGconn *conn, Oid lobjId, int start, int len)
109. {
110. int         lobj_fd;
111. char       *buf;
112. int         nbytes;
113. int         nwritten;
114. int         i;

116. lobj_fd = lo_open(conn, lobjId, INV_WRITE);
117. if (lobj_fd < 0)
118. fprintf(stderr, "cannot open large object %u", lobjId);

120. lo_lseek(conn, lobj_fd, start, SEEK_SET);
121. buf = malloc(len + 1);

123. for (i = 0; i < len; i++)
124. buf[i] = 'X';
125. buf[i] = '\0';

127. nwritten = 0;
128. while (len - nwritten > 0)
129. {
130. nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
131. nwritten += nbytes;
132. if (nbytes <= 0)
133. {
134. fprintf(stderr, "\nWRITE FAILED!\n");
135. break;
136. }
137. }
138. free(buf);
139. fprintf(stderr, "\n");
140. lo_close(conn, lobj_fd);
141. }

144. /*
145. * exportFile -
146. *    export large object "lobjOid" to file "out_filename"
147. *
148. */
149. static void
150. exportFile(PGconn *conn, Oid lobjId, char *filename)
151. {
152. int         lobj_fd;
153. char        buf[BUFSIZE];
154. int         nbytes,
155. tmp;
156. int         fd;

158. /*
159. * open the large object
160. */
161. lobj_fd = lo_open(conn, lobjId, INV_READ);
162. if (lobj_fd < 0)
163. fprintf(stderr, "cannot open large object %u", lobjId);

165. /*
166. * open the file to be written to
167. */
168. fd = open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
169. if (fd < 0)
170. {                           /* error */
171. fprintf(stderr, "cannot open unix file\"%s\"",
172. filename);
173. }

175. /*
176. * read in from the inversion file and write to the Unix file
177. */
178. while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
179. {
180. tmp = write(fd, buf, nbytes);
181. if (tmp < nbytes)
182. {
183. fprintf(stderr, "error while writing \"%s\"",
184. filename);
185. }
186. }

188. lo_close(conn, lobj_fd);
189. close(fd);
190. }

192. static void
193. exit_nicely(PGconn *conn)
194. {
195. PQfinish(conn);
196. exit(1);
197. }

199. int
200. main(int argc, char **argv)
201. {
202. char       *in_filename,
203. *out_filename;
204. char       *database;
205. Oid         lobjOid;
206. PGconn     *conn;
207. PGresult   *res;

209. if (argc != 4)
210. {
211. fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
212. argv[0]);
213. exit(1);
214. }

216. database = argv[1];
217. in_filename = argv[2];
218. out_filename = argv[3];

220. /*
221. * set up the connection
222. */
223. conn = PQsetdb(NULL, NULL, NULL, NULL, database);

225. /* check to see that the backend connection was successfully made */
226. if (PQstatus(conn) != CONNECTION_OK)
227. {
228. fprintf(stderr, "%s", PQerrorMessage(conn));
229. exit_nicely(conn);
230. }

232. /* Set always-secure search path, so malicious users can't take control. */
233. res = PQexec(conn,
234. "SELECT pg_catalog.set_config('search_path', '', false)");
235. if (PQresultStatus(res) != PGRES_TUPLES_OK)
236. {
237. fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
238. PQclear(res);
239. exit_nicely(conn);
240. }
241. PQclear(res);

243. res = PQexec(conn, "begin");
244. PQclear(res);
245. printf("importing file \"%s\" ...\n", in_filename);
246. /*  lobjOid = importFile(conn, in_filename); */
247. lobjOid = lo_import(conn, in_filename);
248. if (lobjOid == 0)
249. fprintf(stderr, "%s\n", PQerrorMessage(conn));
250. else
251. {
252. printf("\tas large object %u.\n", lobjOid);

254. printf("picking out bytes 1000-2000 of the large object\n");
255. pickout(conn, lobjOid, 1000, 1000);

257. printf("overwriting bytes 1000-2000 of the large object with X's\n");
258. overwrite(conn, lobjOid, 1000, 1000);

260. printf("exporting large object to file \"%s\" ...\n", out_filename);
261. /*      exportFile(conn, lobjOid, out_filename); */
262. if (lo_export(conn, lobjOid, out_filename) < 0)
263. fprintf(stderr, "%s\n", PQerrorMessage(conn));
264. }

266. res = PQexec(conn, "end");
267. PQclear(res);
268. PQfinish(conn);
269. return 0;
270. }

示例 4:

1. /*-------------------------------------------------------------------------
2. *
3. * testlo64.c
4. *      test using large objects with libpq using 64-bit APIs
5. *
6. * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
7. * Portions Copyright (c) 1994, Regents of the University of California
8. *
9. *
10. * IDENTIFICATION
11. *      src/test/examples/testlo64.c
12. *
13. *-------------------------------------------------------------------------
14. */
15. #include <stdio.h>
16. #include <stdlib.h>
17. #include <sys/types.h>
18. #include <sys/stat.h>
19. #include <fcntl.h>
20. #include <unistd.h>
21. #include "libpq/libpq-fe.h"
22. #include "libpq/libpq-fs.h"

24. #define BUFSIZE 1024

26. /*
27. * importFile -
28. *      import file "in_filename" into database as large object "lobjOid"
29. */
30. static Oid importFile(PGconn *conn, char *filename)
31. {
32. Oid lobjId;
33. int lobj_fd;
34. char buf[BUFSIZE];
35. int nbytes, tmp;
36. int fd;

38. /*
39. * open the file to be read in
40. */
41. fd = open(filename, O_RDONLY, 0666);
42. if (fd < 0) {
43. fprintf(stderr, "cannot open unix file\"%s\"\n", filename);
44. }

46. /*
47. * create the large object
48. */
49. lobjId = lo_creat(conn, INV_READ | INV_WRITE);
50. if (lobjId == 0)
51. fprintf(stderr, "cannot create large object");
52. lobj_fd = lo_open(conn, lobjId, INV_WRITE);

54. /*
55. * read in from the Unix file and write to the inversion file
56. */
57. while ((nbytes = read(fd, buf, BUFSIZE)) > 0) {
58. tmp = lo_write(conn, lobj_fd, buf, nbytes);
59. if (tmp < nbytes)
60. fprintf(stderr, "error while reading \"%s\"", filename);
61. }

63. close(fd);
64. lo_close(conn, lobj_fd);
65. return lobjId;
66. }

68. static void pickout(PGconn *conn, Oid lobjId, long int start, int len)
69. {
70. int lobj_fd;
71. char *buf;
72. int nbytes;
73. int nread;

75. lobj_fd = lo_open(conn, lobjId, INV_READ);
76. if (lobj_fd < 0)
77. fprintf(stderr, "cannot open large object %u", lobjId);

79. if (lo_lseek64(conn, lobj_fd, start, SEEK_SET) < 0)
80. fprintf(stderr, "error in lo_lseek64: %s", PQerrorMessage(conn));

82. if (lo_tell64(conn, lobj_fd) != start)
83. fprintf(stderr, "error in lo_tell64: %s", PQerrorMessage(conn));

85. buf = (char *)malloc(len + 1);
86. nread = 0;
87. while (len - nread > 0) {
88. nbytes = lo_read(conn, lobj_fd, buf, len - nread);
89. buf[nbytes] = '\0';
90. fprintf(stderr, ">>> %s", buf);
91. nread += nbytes;
92. if (nbytes <= 0)
93. break; /* no more data? */
94. }
95. free(buf);
96. fprintf(stderr, "\n");
97. lo_close(conn, lobj_fd);
98. }

100. static void overwrite(PGconn *conn, Oid lobjId, long int start, int len)
101. {
102. int lobj_fd;
103. char *buf;
104. int nbytes;
105. int nwritten;
106. int i;

108. lobj_fd = lo_open(conn, lobjId, INV_WRITE);
109. if (lobj_fd < 0)
110. fprintf(stderr, "cannot open large object %u", lobjId);

112. if (lo_lseek64(conn, lobj_fd, start, SEEK_SET) < 0)
113. fprintf(stderr, "error in lo_lseek64: %s", PQerrorMessage(conn));

115. buf = (char *)malloc(len + 1);
116. for (i = 0; i < len; i++)
117. buf[i] = 'X';
118. buf[i] = '\0';
119. nwritten = 0;
120. while (len - nwritten > 0) {
121. nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
122. nwritten += nbytes;
123. if (nbytes <= 0) {
124. fprintf(stderr, "\nWRITE FAILED!\n");
125. break;
126. }
127. }
128. free(buf);
129. fprintf(stderr, "\n");
130. lo_close(conn, lobj_fd);
131. }

133. static void my_truncate(PGconn *conn, Oid lobjId, long int len)
134. {
135. int lobj_fd;
136. lobj_fd = lo_open(conn, lobjId, INV_READ | INV_WRITE);
137. if (lobj_fd < 0)
138. fprintf(stderr, "cannot open large object %u", lobjId);

140. if (lo_truncate64(conn, lobj_fd, len) < 0)
141. fprintf(stderr, "error in lo_truncate64: %s", PQerrorMessage(conn));
142. lo_close(conn, lobj_fd);
143. }

146. /*
147. * exportFile -
148. *      export large object "lobjOid" to file "out_filename"
149. */
150. static void exportFile(PGconn *conn, Oid lobjId, char *filename)
151. {
152. char buf[BUFSIZE];
153. int nbytes, tmp, lobj_fd, fd;

155. /*
156. * open the large object
157. */
158. lobj_fd = lo_open(conn, lobjId, INV_READ);
159. if (lobj_fd < 0)
160. fprintf(stderr, "cannot open large object %u", lobjId);

162. /*
163. * open the file to be written to
164. */
165. fd = open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
166. if (fd < 0) {
167. fprintf(stderr, "cannot open unix file\"%s\"", filename);
168. }

170. /*
171. * read in from the inversion file and write to the Unix file
172. */
173. while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0) {
174. tmp = write(fd, buf, nbytes);
175. if (tmp < nbytes) {
176. fprintf(stderr, "error while writing \"%s\"", filename);
177. }
178. }

180. lo_close(conn, lobj_fd);
181. close(fd);
182. return;
183. }

185. static void exit_nicely(PGconn *conn)
186. {
187. PQfinish(conn);
188. exit(1);
189. }

191. int main(int argc, char **argv)
192. {
193. char *database, *in_filename, *out_filename, *out_filename2;
194. Oid lobjOid;
195. PGconn *conn;
196. PGresult *res;

198. if (argc != 5) {
199. fprintf(stderr, "Usage: %s database_name in_filename out_filename out_filename2\n", argv[0]);
200. exit(1);
201. }

203. database = argv[1];
204. in_filename = argv[2];
205. out_filename = argv[3];
206. out_filename2 = argv[4];

208. /*
209. * set up the connection
210. */
211. conn = PQsetdb(NULL, NULL, NULL, NULL, database);

213. /* check to see that the backend connection was successfully made */
214. if (PQstatus(conn) != CONNECTION_OK) {
215. fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
216. exit_nicely(conn);
217. }

219. /* Set always-secure search path, so malicous users can't take control. */
220. res = PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)");
221. if (PQresultStatus(res) != PGRES_TUPLES_OK) {
222. fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
223. PQclear(res);
224. exit_nicely(conn);
225. }
226. PQclear(res);
227. res = PQexec(conn, "begin");
228. PQclear(res);
229. printf("importing file \"%s\" ...\n", in_filename);

231. /* lobjOid = importFile(conn, in_filename); */
232. lobjOid = lo_import(conn, in_filename);
233. if (lobjOid == 0) {
234. fprintf(stderr, "%s\n", PQerrorMessage(conn));
235. } else {
236. printf("\tas large object %u.\n", lobjOid);
237. printf("picking out bytes 4294967000-4294968000 of the large object\n");
238. pickout(conn, lobjOid, 4294967000U, 1000);
239. printf("overwriting bytes 4294967000-4294968000 of the large object with X's\n");
240. overwrite(conn, lobjOid, 4294967000U, 1000);
241. printf("exporting large object to file \"%s\" ...\n", out_filename);

243. /* exportFile(conn, lobjOid, out_filename); */
244. if (lo_export(conn, lobjOid, out_filename) < 0)
245. fprintf(stderr, "%s\n", PQerrorMessage(conn));
246. printf("truncating to 3294968000 bytes\n");
247. my_truncate(conn, lobjOid, 3294968000U);
248. printf("exporting truncated large object to file \"%s\" ...\n", out_filename2);
249. if (lo_export(conn, lobjOid, out_filename2) < 0)
250. fprintf(stderr, "%s\n", PQerrorMessage(conn));
251. }

253. res = PQexec(conn, "end");
254. PQclear(res);
255. PQfinish(conn);
256. return 0;
257. }