* src/test/examples/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 SCHEMA testlibpq3;
* SET search_path = testlibpq3;
* 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:
* t = (11 bytes) 'joe's place'
* b = (5 bytes) \000\001\002\003\004
* t = (8 bytes) 'ho there'
* b = (5 bytes) \004\003\002\001\000
exit_nicely(PGconn *conn)
* 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.
show_binary_results(PGresult *res)
/* 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++)
/* 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
* 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]);
main(int argc, char **argv)
const char *paramValues[1];
* 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.
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",
/* Set always-secure search path, so malicous users can't take control. */
res = PQexec(conn, "SET search_path = testlibpq3");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
fprintf(stderr, "SET failed: %s", PQerrorMessage(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";
"SELECT * FROM test1 WHERE t = $1",
NULL, /* let the backend deduce param type */
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));
show_binary_results(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
/* 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 */
"SELECT * FROM test1 WHERE i = $1::int4",
NULL, /* let the backend deduce param type */
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
show_binary_results(res);
/* close the connection to the database and cleanup */