应用案例 | PostgreSQL libpq 网络库接口操作数据库示例 postgresql

本文首发于 2015-11-12 09:23:46

前言本文成文较早，测试程序验证的是 PostgreSQL 9.3 版本的 libpq，理论上，对其他版本也应该适用。
【应用案例 | PostgreSQL libpq 网络库接口操作数据库示例】关于 libpq 各个函数接口的说明，参考：http://www.postgres.cn/docs/9...
示例1

/* * testlibpq.c * *Test the C version of libpq, the PostgreSQL frontend library. */ #include #include #include static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn*conn; PGresult*res; intnFields; inti, 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. */ 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. */ /* 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(res); /* * Fetch rows from pg_database, the system catalog of databases */ 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; }

示例2

/* * testlibpq2.c *Test of the asynchronous notification interface * * Start this program, then from psql in another window do *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): * *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 #include #include #include #include #include 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; intnnotifies; /* * 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. */ 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. */ 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(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. */ intsock; fd_setinput_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; }

示例3

/* * testlibpq3.c *Test out-of-line parameters and binary I/O. * * Before running this, populate a database with the following commands * (provided in 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 #include #include #include #include /* for ntohl/htonl */ #include #include 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. */ static void show_binary_results(PGresult *res) { inti, j; inti_fnum, t_fnum, b_fnum; /* Use PQfnumber to avoid assumptions about field order in result */ 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; intblen; intival; /* 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. */ 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. */ 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]; intparamLengths[1]; intparamFormats[1]; uint32_tbinaryIntVal; /* * 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. */ 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. */ /* 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. */ /* Convert integer value "2" to network byte order */ binaryIntVal = htonl((uint32_t) 2); /* Set up parameter arrays for 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; }

欢迎关注我的微信公众号【数据库内核】：分享主流开源数据库和存储引擎相关技术。

文章图片

标题	网址
GitHub	https://dbkernel.github.io
知乎	https://www.zhihu.com/people/...
思否（SegmentFault）	https://segmentfault.com/u/db...
掘金	https://juejin.im/user/5e9d3e...
开源中国（oschina）	https://my.oschina.net/dbkernel
博客园（cnblogs）	https://www.cnblogs.com/dbkernel