codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>34.22. Example Programs</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="libpq-build.html" title="34.21. Building libpq Programs" /><link rel="next" href="largeobjects.html" title="Chapter 35. Large Objects" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">34.22. Example Programs</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="libpq-build.html" title="34.21. Building libpq Programs">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><th width="60%" align="center">Chapter 34. <span class="application">libpq</span> — C Library</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="largeobjects.html" title="Chapter 35. Large Objects">Next</a></td></tr></table><hr /></div><div class="sect1" id="LIBPQ-EXAMPLE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">34.22. Example Programs <a href="#LIBPQ-EXAMPLE" class="id_link">#</a></h2></div></div></div><p>3 These examples and others can be found in the4 directory <code class="filename">src/test/examples</code> in the source code5 distribution.6 </p><div class="example" id="LIBPQ-EXAMPLE-1"><p class="title"><strong>Example 34.1. <span class="application">libpq</span> Example Program 1</strong></p><div class="example-contents"><pre class="programlisting">7 8/*9 * src/test/examples/testlibpq.c10 *11 *12 * testlibpq.c13 *14 * Test the C version of libpq, the PostgreSQL frontend library.15 */16#include <stdio.h>17#include <stdlib.h>18#include "libpq-fe.h"19 20static void21exit_nicely(PGconn *conn)22{23 PQfinish(conn);24 exit(1);25}26 27int28main(int argc, char **argv)29{30 const char *conninfo;31 PGconn *conn;32 PGresult *res;33 int nFields;34 int i,35 j;36 37 /*38 * If the user supplies a parameter on the command line, use it as the39 * conninfo string; otherwise default to setting dbname=postgres and using40 * environment variables or defaults for all other connection parameters.41 */42 if (argc > 1)43 conninfo = argv[1];44 else45 conninfo = "dbname = postgres";46 47 /* Make a connection to the database */48 conn = PQconnectdb(conninfo);49 50 /* Check to see that the backend connection was successfully made */51 if (PQstatus(conn) != CONNECTION_OK)52 {53 fprintf(stderr, "%s", PQerrorMessage(conn));54 exit_nicely(conn);55 }56 57 /* Set always-secure search path, so malicious users can't take control. */58 res = PQexec(conn,59 "SELECT pg_catalog.set_config('search_path', '', false)");60 if (PQresultStatus(res) != PGRES_TUPLES_OK)61 {62 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));63 PQclear(res);64 exit_nicely(conn);65 }66 67 /*68 * Should PQclear PGresult whenever it is no longer needed to avoid memory69 * leaks70 */71 PQclear(res);72 73 /*74 * Our test case here involves using a cursor, for which we must be inside75 * a transaction block. We could do the whole thing with a single76 * PQexec() of "select * from pg_database", but that's too trivial to make77 * a good example.78 */79 80 /* Start a transaction block */81 res = PQexec(conn, "BEGIN");82 if (PQresultStatus(res) != PGRES_COMMAND_OK)83 {84 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));85 PQclear(res);86 exit_nicely(conn);87 }88 PQclear(res);89 90 /*91 * Fetch rows from pg_database, the system catalog of databases92 */93 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");94 if (PQresultStatus(res) != PGRES_COMMAND_OK)95 {96 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));97 PQclear(res);98 exit_nicely(conn);99 }100 PQclear(res);101 102 res = PQexec(conn, "FETCH ALL in myportal");103 if (PQresultStatus(res) != PGRES_TUPLES_OK)104 {105 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));106 PQclear(res);107 exit_nicely(conn);108 }109 110 /* first, print out the attribute names */111 nFields = PQnfields(res);112 for (i = 0; i < nFields; i++)113 printf("%-15s", PQfname(res, i));114 printf("\n\n");115 116 /* next, print out the rows */117 for (i = 0; i < PQntuples(res); i++)118 {119 for (j = 0; j < nFields; j++)120 printf("%-15s", PQgetvalue(res, i, j));121 printf("\n");122 }123 124 PQclear(res);125 126 /* close the portal ... we don't bother to check for errors ... */127 res = PQexec(conn, "CLOSE myportal");128 PQclear(res);129 130 /* end the transaction */131 res = PQexec(conn, "END");132 PQclear(res);133 134 /* close the connection to the database and cleanup */135 PQfinish(conn);136 137 return 0;138}139 140</pre></div></div><br class="example-break" /><div class="example" id="LIBPQ-EXAMPLE-2"><p class="title"><strong>Example 34.2. <span class="application">libpq</span> Example Program 2</strong></p><div class="example-contents"><pre class="programlisting">141 142/*143 * src/test/examples/testlibpq2.c144 *145 *146 * testlibpq2.c147 * Test of the asynchronous notification interface148 *149 * Start this program, then from psql in another window do150 * NOTIFY TBL2;151 * Repeat four times to get this program to exit.152 *153 * Or, if you want to get fancy, try this:154 * populate a database with the following commands155 * (provided in src/test/examples/testlibpq2.sql):156 *157 * CREATE SCHEMA TESTLIBPQ2;158 * SET search_path = TESTLIBPQ2;159 * CREATE TABLE TBL1 (i int4);160 * CREATE TABLE TBL2 (i int4);161 * CREATE RULE r1 AS ON INSERT TO TBL1 DO162 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);163 *164 * Start this program, then from psql do this four times:165 *166 * INSERT INTO TESTLIBPQ2.TBL1 VALUES (10);167 */168 169#ifdef WIN32170#include <windows.h>171#endif172#include <stdio.h>173#include <stdlib.h>174#include <string.h>175#include <errno.h>176#include <sys/select.h>177#include <sys/time.h>178#include <sys/types.h>179 180#include "libpq-fe.h"181 182static void183exit_nicely(PGconn *conn)184{185 PQfinish(conn);186 exit(1);187}188 189int190main(int argc, char **argv)191{192 const char *conninfo;193 PGconn *conn;194 PGresult *res;195 PGnotify *notify;196 int nnotifies;197 198 /*199 * If the user supplies a parameter on the command line, use it as the200 * conninfo string; otherwise default to setting dbname=postgres and using201 * environment variables or defaults for all other connection parameters.202 */203 if (argc > 1)204 conninfo = argv[1];205 else206 conninfo = "dbname = postgres";207 208 /* Make a connection to the database */209 conn = PQconnectdb(conninfo);210 211 /* Check to see that the backend connection was successfully made */212 if (PQstatus(conn) != CONNECTION_OK)213 {214 fprintf(stderr, "%s", PQerrorMessage(conn));215 exit_nicely(conn);216 }217 218 /* Set always-secure search path, so malicious users can't take control. */219 res = PQexec(conn,220 "SELECT pg_catalog.set_config('search_path', '', false)");221 if (PQresultStatus(res) != PGRES_TUPLES_OK)222 {223 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));224 PQclear(res);225 exit_nicely(conn);226 }227 228 /*229 * Should PQclear PGresult whenever it is no longer needed to avoid memory230 * leaks231 */232 PQclear(res);233 234 /*235 * Issue LISTEN command to enable notifications from the rule's NOTIFY.236 */237 res = PQexec(conn, "LISTEN TBL2");238 if (PQresultStatus(res) != PGRES_COMMAND_OK)239 {240 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));241 PQclear(res);242 exit_nicely(conn);243 }244 PQclear(res);245 246 /* Quit after four notifies are received. */247 nnotifies = 0;248 while (nnotifies < 4)249 {250 /*251 * Sleep until something happens on the connection. We use select(2)252 * to wait for input, but you could also use poll() or similar253 * facilities.254 */255 int sock;256 fd_set input_mask;257 258 sock = PQsocket(conn);259 260 if (sock < 0)261 break; /* shouldn't happen */262 263 FD_ZERO(&input_mask);264 FD_SET(sock, &input_mask);265 266 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)267 {268 fprintf(stderr, "select() failed: %s\n", strerror(errno));269 exit_nicely(conn);270 }271 272 /* Now check for input */273 PQconsumeInput(conn);274 while ((notify = PQnotifies(conn)) != NULL)275 {276 fprintf(stderr,277 "ASYNC NOTIFY of '%s' received from backend PID %d\n",278 notify->relname, notify->be_pid);279 PQfreemem(notify);280 nnotifies++;281 PQconsumeInput(conn);282 }283 }284 285 fprintf(stderr, "Done.\n");286 287 /* close the connection to the database and cleanup */288 PQfinish(conn);289 290 return 0;291}292 293</pre></div></div><br class="example-break" /><div class="example" id="LIBPQ-EXAMPLE-3"><p class="title"><strong>Example 34.3. <span class="application">libpq</span> Example Program 3</strong></p><div class="example-contents"><pre class="programlisting">294 295/*296 * src/test/examples/testlibpq3.c297 *298 *299 * testlibpq3.c300 * Test out-of-line parameters and binary I/O.301 *302 * Before running this, populate a database with the following commands303 * (provided in src/test/examples/testlibpq3.sql):304 *305 * CREATE SCHEMA testlibpq3;306 * SET search_path = testlibpq3;307 * SET standard_conforming_strings = ON;308 * CREATE TABLE test1 (i int4, t text, b bytea);309 * INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004');310 * INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000');311 *312 * The expected output is:313 *314 * tuple 0: got315 * i = (4 bytes) 1316 * t = (11 bytes) 'joe's place'317 * b = (5 bytes) \000\001\002\003\004318 *319 * tuple 0: got320 * i = (4 bytes) 2321 * t = (8 bytes) 'ho there'322 * b = (5 bytes) \004\003\002\001\000323 */324 325#ifdef WIN32326#include <windows.h>327#endif328 329#include <stdio.h>330#include <stdlib.h>331#include <stdint.h>332#include <string.h>333#include <sys/types.h>334#include "libpq-fe.h"335 336/* for ntohl/htonl */337#include <netinet/in.h>338#include <arpa/inet.h>339 340 341static void342exit_nicely(PGconn *conn)343{344 PQfinish(conn);345 exit(1);346}347 348/*349 * This function prints a query result that is a binary-format fetch from350 * a table defined as in the comment above. We split it out because the351 * main() function uses it twice.352 */353static void354show_binary_results(PGresult *res)355{356 int i,357 j;358 int i_fnum,359 t_fnum,360 b_fnum;361 362 /* Use PQfnumber to avoid assumptions about field order in result */363 i_fnum = PQfnumber(res, "i");364 t_fnum = PQfnumber(res, "t");365 b_fnum = PQfnumber(res, "b");366 367 for (i = 0; i < PQntuples(res); i++)368 {369 char *iptr;370 char *tptr;371 char *bptr;372 int blen;373 int ival;374 375 /* Get the field values (we ignore possibility they are null!) */376 iptr = PQgetvalue(res, i, i_fnum);377 tptr = PQgetvalue(res, i, t_fnum);378 bptr = PQgetvalue(res, i, b_fnum);379 380 /*381 * The binary representation of INT4 is in network byte order, which382 * we'd better coerce to the local byte order.383 */384 ival = ntohl(*((uint32_t *) iptr));385 386 /*387 * The binary representation of TEXT is, well, text, and since libpq388 * was nice enough to append a zero byte to it, it'll work just fine389 * as a C string.390 *391 * The binary representation of BYTEA is a bunch of bytes, which could392 * include embedded nulls so we have to pay attention to field length.393 */394 blen = PQgetlength(res, i, b_fnum);395 396 printf("tuple %d: got\n", i);397 printf(" i = (%d bytes) %d\n",398 PQgetlength(res, i, i_fnum), ival);399 printf(" t = (%d bytes) '%s'\n",400 PQgetlength(res, i, t_fnum), tptr);401 printf(" b = (%d bytes) ", blen);402 for (j = 0; j < blen; j++)403 printf("\\%03o", bptr[j]);404 printf("\n\n");405 }406}407 408int409main(int argc, char **argv)410{411 const char *conninfo;412 PGconn *conn;413 PGresult *res;414 const char *paramValues[1];415 int paramLengths[1];416 int paramFormats[1];417 uint32_t binaryIntVal;418 419 /*420 * If the user supplies a parameter on the command line, use it as the421 * conninfo string; otherwise default to setting dbname=postgres and using422 * environment variables or defaults for all other connection parameters.423 */424 if (argc > 1)425 conninfo = argv[1];426 else427 conninfo = "dbname = postgres";428 429 /* Make a connection to the database */430 conn = PQconnectdb(conninfo);431 432 /* Check to see that the backend connection was successfully made */433 if (PQstatus(conn) != CONNECTION_OK)434 {435 fprintf(stderr, "%s", PQerrorMessage(conn));436 exit_nicely(conn);437 }438 439 /* Set always-secure search path, so malicious users can't take control. */440 res = PQexec(conn, "SET search_path = testlibpq3");441 if (PQresultStatus(res) != PGRES_COMMAND_OK)442 {443 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));444 PQclear(res);445 exit_nicely(conn);446 }447 PQclear(res);448 449 /*450 * The point of this program is to illustrate use of PQexecParams() with451 * out-of-line parameters, as well as binary transmission of data.452 *453 * This first example transmits the parameters as text, but receives the454 * results in binary format. By using out-of-line parameters we can avoid455 * a lot of tedious mucking about with quoting and escaping, even though456 * the data is text. Notice how we don't have to do anything special with457 * the quote mark in the parameter value.458 */459 460 /* Here is our out-of-line parameter value */461 paramValues[0] = "joe's place";462 463 res = PQexecParams(conn,464 "SELECT * FROM test1 WHERE t = $1",465 1, /* one param */466 NULL, /* let the backend deduce param type */467 paramValues,468 NULL, /* don't need param lengths since text */469 NULL, /* default to all text params */470 1); /* ask for binary results */471 472 if (PQresultStatus(res) != PGRES_TUPLES_OK)473 {474 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));475 PQclear(res);476 exit_nicely(conn);477 }478 479 show_binary_results(res);480 481 PQclear(res);482 483 /*484 * In this second example we transmit an integer parameter in binary form,485 * and again retrieve the results in binary form.486 *487 * Although we tell PQexecParams we are letting the backend deduce488 * parameter type, we really force the decision by casting the parameter489 * symbol in the query text. This is a good safety measure when sending490 * binary parameters.491 */492 493 /* Convert integer value "2" to network byte order */494 binaryIntVal = htonl((uint32_t) 2);495 496 /* Set up parameter arrays for PQexecParams */497 paramValues[0] = (char *) &binaryIntVal;498 paramLengths[0] = sizeof(binaryIntVal);499 paramFormats[0] = 1; /* binary */500 501 res = PQexecParams(conn,502 "SELECT * FROM test1 WHERE i = $1::int4",503 1, /* one param */504 NULL, /* let the backend deduce param type */505 paramValues,506 paramLengths,507 paramFormats,508 1); /* ask for binary results */509 510 if (PQresultStatus(res) != PGRES_TUPLES_OK)511 {512 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));513 PQclear(res);514 exit_nicely(conn);515 }516 517 show_binary_results(res);518 519 PQclear(res);520 521 /* close the connection to the database and cleanup */522 PQfinish(conn);523 524 return 0;525}526 527</pre></div></div><br class="example-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="libpq-build.html" title="34.21. Building libpq Programs">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="largeobjects.html" title="Chapter 35. Large Objects">Next</a></td></tr><tr><td width="40%" align="left" valign="top">34.21. Building <span class="application">libpq</span> Programs </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 35. Large Objects</td></tr></table></div></body></html>