| PostgreSQL Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 408 pages ISBN 0954612035 RRP £19.95 ($34.95) Sales of this book support the PostgreSQL project! Get a printed copy>>> |
1.19 Example Programs
These examples and others can be found in the directory ‘src/test/examples’ in the source code distribution.
libpq Example Program 1:
/* testlibpq.c Test the C version of libpq, the PostgreSQL
frontend library. */
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn * conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields;
int i, 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;
}
libpq Example Program 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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include "libpq-fe.h"
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;
int nnotifies;
/* 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. */
int sock;
fd_set input_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;
}
libpq Example Program 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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
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)
{
int i, j;
int i_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;
int blen;
int ival;
/* 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];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
/* 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",
/* one param */
1,
/* let the backend deduce param type */
NULL, paramValues, NULL, /* don't need
param
lengths
since text */
NULL, /* default to all text params */
/* ask for binary results */
1);
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",
/* one param */
1,
/* let the backend deduce param type */
NULL, paramValues, paramLengths,
paramFormats,
/* ask for binary results */
1);
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;
}
| ISBN 0954612035 | PostgreSQL Reference Manual - Volume 2 - Programming Guide | See the print edition |