Client 3—Simple Processing—PQexec() and PQprint()

Now let's turn our attention to the task of processing a query. I'll start by showing a simple example—you'll connect to a database, execute a hard-wired query, process the results, clean up, and exit.

 1 /*
 2 ** File: client3.c
 3 */
 4
 5 #include <stdlib.h>
 6 #include <libpq-fe.h>
 7
 8 void process_query( PGconn * connection, const char * query_text )
 9 {
10   PGresult  *       result;
11   PQprintOpt        options = {0};
12
13   if(( result = PQexec( connection, query_text )) == NULL )
14   {
15     printf( "%s
", PQerrorMessage( connection ));
16     return;
17   }
18
19   options.header    = 1;    /* Ask for column headers            */
20   options.align     = 1;    /* Pad short columns for alignment   */
21   options.fieldSep  = "|";  /* Use a pipe as the field separator */
22
23   PQprint( stdout, result, &options );
24
25   PQclear( result );
26 }
27
28 int main( int argc, char * argv[] )
29 {
30   PGconn * connection;
31
32   if( argc != 2 )
33   {
34     printf( "usage  : %s "connection-string"
", argv[0] );
35     printf( "example: %s "user=myname password=cows"
", argv[0]);
36     exit( 1 );
37   }
38
39   if(( connection = PQconnectdb( argv[1] )) == NULL )
40   {
41     printf( "Fatal error - unable to allocate connection
" );
42     exit( 1 );
43   }
44
45   if( PQstatus( connection ) != CONNECTION_OK )
46     printf( "%s
", PQerrorMessage( connection ));
47   else
48   {
49     process_query( connection, "SELECT * FROM rentals" );
50   }
51
52   PQfinish( connection );
53
54   exit( 0 );
55 }

The interesting part of this program is the process_query() function (lines 8-26). client3.c starts by calling PQexec(). This function is used to execute a query in a synchronous (that is, blocking) fashion. (Like the connection API, there are two methods to execute a query: synchronous and asynchronous. I'll show you the asynchronous query functions later.) When you call PQexec(), you provide a connection object(a PGconn pointer) and a command string. PQexec() returns a pointer to a PGresult object. A PGresult is similar to a PGconn—it is an opaque handle and you can query the object for different pieces of information (such as “Did my query succeed or fail?”). A PGresult object represents the result of executing a command. When you execute a query (as opposed to an INSERT command), the entire result set (including meta-data) of the query is accessible through the object. A PGresult object also provides access to any error messages that may result from executing a command.

I'm going to cheat here. libpq provides a handy function called PQprint() that does all the dirty work required to print the results of a query.

I'll use PQprint() here because it is such a simple way to print a result set. Later, I'll show you how to produce much of the same functionality yourself.

Before you can call PQprint(), you must construct a PQprintOpt object. At line 11, you initialize the PQprintOpt object and then set the three members that you care about (header, align, and fieldSep) at lines 19-21. PQprint() requires three arguments: a FILE pointer (in this case, specify stdout), a PGresult pointer (returned from PQexec()), and a pointer to a PGprintOpt object. PQprint() formats the results of the query and prints them to the file that you specified. If the query fails, PQprint() will print an appropriate error message.

Remember that PQexec() returned a pointer to a PGresult object—you need to free that object because PQclear() will destroy a PGresult object.

When you are finished processing the result set, free the PGresult resources using PQclear() (see line 25). It's important to PQclear() all PGresult objects when you are done with them. When libpq executes a query on your behalf, the entire result set of the query is accessible through a PGresult object. That means that if you execute a query that returns 100,000 rows, the PGresult object will consume enough memory to hold all 100,000 rows.

Results Returned by PQexec()

Many client applications need to do more than just print column values. After executing a command, you can obtain a lot of information about the results of the command through the PGresult object returned by PQexec().

The most obvious piece of information that you can obtain from a PGresult pointer is whether the command succeeded or failed. If the command succeeded, PQresultStatus() will return either PGRES_COMMAND_OK or PGRES_TUPLES_OK. PGRES_TUPLES_OK means that you successfully executed a query and there are zero or more rows available for processing. PGRES_COMMAND_OK means that you successfully executed some command other than SELECT; an INSERT command for example. If the query causes an error, you will get back a result of PGRES_FATAL_ERROR or PGRES_NONFATAL_ERROR. (There are other values that PQresultStatus() can return; see the PostgreSQL Programmer's Guide for more information.) It's possible that PQexec() will return a NULL PGresult pointer if libpq runs out of memory—you should treat that as a PGRES_FATAL_ERROR.

If a command fails, you can use PQresultErrorMessage() to find the reason for failure. To call PQresultErrorMessage(), pass the PGresult pointer that was returned by PQexec(). PQresultErrorMessage() returns a pointer to the null-terminated string containing the reason for failure (if you call PQresultErrorMessage() for a successful query, you'll get back a pointer to an empty string).

I'll modify the process_query() function from the previous example (client3.c) to show how to use PQresultStatus() and PQresultErrorMessage():

 1 /*
 2 ** File: client3b.c
 3 */
 4
 5 #include <stdlib.h>
 6 #include <libpq-fe.h>
 7
 8 void process_query( PGconn * connection, const char * query_text )
 9 {
10   PGresult  *       result;
11
12   if(( result = PQexec( connection, query_text )) == NULL )
13   {
14     printf( "%s
", PQerrorMessage( connection ));
15     return;
16   }
17
18   if(( PQresultStatus( result ) == PGRES_COMMAND_OK ) ||
19      ( PQresultStatus( result ) == PGRES_TUPLES_OK ))
20   {
21     PQprintOpt              options = {0};
22
23     options.header    = 1;    /* Ask for column headers           */
24     options.align     = 1;    /* Pad short columns for alignment  */
25     options.fieldSep  = "|";  /* Use a pipe as the field separator*/
26
27     PQprint( stdout, result, &options );
28
29   }
30   else
31   {
32     printf( "%s
", PQresStatus( PQresultStatus( result )));
33     printf( "%s
", PQresultErrorMessage( result ));
34
35   }
36
37   PQclear( result );
38 }
39
40 int main( int argc, char * argv[] )
41 {
42   PGconn * connection;
43
44   if( argc != 2 )
45   {
46     printf( "usage  : %s "connection-string"
", argv[0] );
47     printf( "example: %s "user=myname password=cows"
", argv[0]);
48     exit( 1 );
49   }
50
51   if(( connection = PQconnectdb( argv[1] )) == NULL )
52   {
53     printf( "Fatal error - unable to allocate connection
" );
54     exit( 1 );
55   }
56
57   if( PQstatus( connection ) != CONNECTION_OK )
58     printf( "%s
", PQerrorMessage( connection ));
59   else
60   {
61     process_query( connection, "SELECT * FROM rentals" );
62   }
63
64   PQfinish( connection );
65
66   exit( 0 );
67 }

At lines 18 and 19, this application checks to see whether the command succeeded. If so, it calls PQprint() to print the result set just like you did in client3.c.

If the command failed, you want to tell the user what went wrong. Look closely at line 32. client3b is calling the PQresultStatus() function again, but this time around it calls PQresStatus() with the return value. PQresultStatus() returns the command status in the form of an integer^[1]. The PQresStatus() function translates a value returned by PQresultStatus() into a human-readable string.

^[1] More precisely, PQresultStatus() returns a value of type enum ExecStatusType.

At line 33, the call to PQresultErrorMessage() retrieves the text of the error message.

After you have successfully executed a query (that is, PQresultStatus() has returned either PGRES_COMMAND_OK or PGRES_TUPLES_OK), you are ready to process the actual results. There are three types of information that you can access through a PGresult object. You've already seen the first type of information: success or failure and an error message. The second type of information is metadata, or data about your data. We'll look at metadata next. Finally, you can access the values returned by the command itself—the rows returned by a query or the OID of an affected row in the case of an INSERT or UPDATE.

First, I'll show you how to find the metadata for your query. libpq provides a number of functions that let you find information about the kind of data returned by your query. For example, the PQntuples() function tells you how many rows (or tuples) will be returned from your query.

The following function prints (most of) the metadata returned for a command:

 1 void print_meta_data( PGresult * result )
 2 {
 3   int   col;
 4
 5   printf( "Status: %s
", PQresStatus( PQresultStatus( result )));
 6   printf( "Returned %d rows ", PQntuples( result ));
 7   printf( "with %d columns

", PQnfields( result ));
 8
 9   printf( "Column Type TypeMod Size Name       
" );
10   printf( "------ ---- ------- ---- -----------
" );
11
12   for( col = 0; col < PQnfields( result ); col++ )
13   {
14     printf( "%3d    %4d %7d %4d %s
",
15             col,
16             PQftype( result, col ),
17             PQfmod( result, col ),
18             PQfsize( result, col ),
19             PQfname( result, col ));
20   }
21 }

If you want to try this function, it is included in client3c.c in the sample code for this book. I won't show the complete application here because it is largely the same as client3b.c.

At line 5, client3b prints the success/failure status from the given PQresult object. It uses the same PQresStatus() and PQresultStatus() functions described earlier, but I've included them in this example because they really do return metadata information.

At line 6, the call to PQntuples() retrieves the number of rows returned by the command. PQntuples() returns zero if the command was not a query. PQntuples() also returns zero if the command was a query, but the query happened to return zero rows in the result set. libpq does not consider it an error for a query to return zero rows. In fact, the PQresult object contains all the usual metadata even when a query does not return any rows.

The PQnfields() function (line 7) returns the number of columns in the result set. Line PQntuples(), PQnfields() returns zero for commands other than SELECT.

The naming convention for the metadata functions is a little confusing at first. PQntuples() returns the number of rows in the result set. PQnfields() returns the number of columns in the result set. A tuple is the same thing as a row. A field is the same thing as a column^[2].

^[2] Technically speaking, a tuple is a version of a row. PostgreSQL uses a concurrency system known as multi-version concurrency control (MVCC). In MVCC, the database can contain multiple versions of the same row. There is also a slight difference between a field and a column. A column is stored in a table. A field is the result of an expression. A column is a valid expression, so a column can be considered a field, but a field is not necessarily a column.

At line 16, the call to PQftype() returns the data type for a given column. The PQftype(), PQfmod(), and PQfsize() functions work together to tell you about the format of the data in a given column.

PQftype() returns a value of type OID. The value returned by PQftype() corresponds to the object-id (OID) of a row in the pg_type system table. (In Chapter 6, “Extending PostgreSQL,” you learned that data type descriptions are stored in pg_type.) You can find the OIDs for predefined data types in the catalog/pg_type.h PostgreSQL header file. PQfmod() returns a value that, in theory, gives you more detailed information about a data type. The values returned by PQfmod() are type-specific and are not documented. You can use the format_type()^[3] function to convert values returned by PQftype() and PQfmod() into a human-readable string. PQfsize() returns the number of bytes required to hold a value on the server. For variable-length data types, PQfsize() returns -1.

^[3] format_type() is not a libpq function. It is a server function that you can call from a SELECT command. For example, SELECT format_type( atttpyid, atttypmod ) FROM pg_attribute.

It turns out that the information returned by PQftype(), PQfmod(), and PQfsize() is not all that useful in most applications. In most cases, the field values returned to your application will be null-terminated strings. For example, if you SELECT a date column, the date values will be converted into string form before it gets to your application. The same is true for numeric values. It is possible to request raw data values (that is, values that have not been converted into string form). I'll show you how to do that a little later.

You can also find information about the table (if any) that each column belongs to. If a given column comes from a table, you can find the OID of that table by calling the PQftable() function. If, instead, the column is a computed value, PQftable() returns the value InvalidOid. If a given column comes directly from a table, you can find the column number (within the table) by calling PQftablecol(). PQftablecol() returns 1 for the first column in a table, 2 for the second column, and so on. The order of the columns within a table (as defined by PQftablecol()) matches what you would see if you executed a SELECT * from that table. PQftablecol() returns zero for computed columns.

The last two metadata functions are PQfname() and PQfnumber(). PQfname() returns the name of the given column in the result set. PQfnumber() returns the column number of the named column.

Now that you know how to retrieve the metadata for a query, let's see how to actually retrieve the data. In this example, we'll replace the earlier calls to PQprint() with our own function.

 1 /*
 2 ** File: client3d.c
 3 */
 4
 5 #include <stdlib.h>
 6 #include <string.h>
 7 #include <libpq-fe.h>
 8
 9 #define MAX_PRINT_LEN       40
10
11 static char separator[MAX_PRINT_LEN+1];
12
13 void print_result_set( PGresult * result )
14 {
15   int          col;
16   int          row;
17   int            * sizes;
18
19 /*
20 **  Compute the size for each column
21 */
22   sizes = (int *)calloc( PQnfields( result ), sizeof( int ));
23
24   for( col = 0; col < PQnfields( result ); col++ )
25   {
26     int     len = 0;
27
28     for( row = 0; row < PQntuples( result ); row++ )
29     {
30       if( PQgetisnull( result, row, col ))
31         len = 0;
32       else
33         len = PQgetlength( result, row, col );
34
35       if( len > sizes[col] )
36         sizes[col] = len;
37     }
38
39     if(( len = strlen( PQfname( result, col ))) > sizes[col] )
40       sizes[col] = len;
41
42     if( sizes[col] > MAX_PRINT_LEN )
43       sizes[col] = MAX_PRINT_LEN;
44   }
45
46 /*
47 **  Print the field names.
48 */
49   for( col = 0; col < PQnfields( result ); col++ )
50   {
51     printf( "%-*s ", sizes[col], PQfname( result, col ));
52   }
53
54   printf( "
" );
55
56 /*
57 **  Print the separator line
58 */
59   memset( separator, '-', MAX_PRINT_LEN );
60
61   for( col = 0; col < PQnfields( result ); col++ )
62   {
63     printf( "%*.*s ", sizes[col], sizes[col], separator );
64   }
65
66   printf( "
" );
67
68 /*
69 **  Now loop through each of the tuples returned by
70 **  our query and print the results.
71 */
72   for( row = 0; row < PQntuples( result ); row++ )
73   {
74     for( col = 0; col < PQnfields( result ); col++ )
75     {
76       if( PQgetisnull( result, row, col ))
77         printf( "%*s", sizes[col], "" );
78       else
79         printf( "%*s ", sizes[col], PQgetvalue( result, row, col ));
80     }
81
82     printf( "
" );
83
84   }
85   printf( "(%d rows)

", PQntuples( result ));
86   free( sizes );
87 }

This function (print_result_set()) replaces PQprint().

The real work in this function is finding the width of each column. For each column in the result set, you must search through all rows to find the widest value in that column. At line 22, print_result_set() allocates an array (sizes[]) of integers to hold the column widths. Lines 24 through 44 fill in the sizes[] array. The PQgetisnull() function tells you whether a given column is NULL in the current row. If you find a NULL field, consider it to have a length of 0. Use the PQgetlength() function to find the length of each value.

Notice that we ensure that each column is wide enough to hold the column name.

The limit to each column is MAX_PRINT_LEN characters. This is a rather arbitrary decision that you can certainly change.

After computing the column widths the code at lines 46-55 will print the name of each column followed by a line of separator characters.

At lines 68 through 84, you loop through every row in the result set and print each column value. The PQgetvalue() function returns a pointer to the value for a given row and column. Because this client has not requested a BINARY cursor (I'll talk about those soon), each data value comes to you in the form of a null-terminated string.

Finally, at line 86, print_result_set() frees up the resource that it allocated (sizes[]) and returns.

 89 void process_query( PGconn * connection, const char * query_text )
 90 {
 91   PGresult  *       result;
 92
 93   if(( result = PQexec( connection, query_text )) == NULL )
 94   {
 95     printf( "%s
", PQerrorMessage( connection ));
 96     return;
 97   }
 98
 99   if( PQresultStatus( result ) == PGRES_TUPLES_OK )
100   {
101     print_result_set( result );
102   }
103   else if( PQresultStatus( result ) == PGRES_COMMAND_OK )
104   {
105     printf( "%s", PQcmdStatus( result ));
106
107     if( strlen( PQcmdTuples( result )))
108       printf( " - %s rows

", PQcmdTuples( result ));
109     else
110       printf( "

" );
111   }
112   else
113   {
114       printf( "%s

", PQresultErrorMessage( result ));
115   }
116
117   PQclear( result );
118 }

This function (process_query()) is not very complex. It executes the given command and prints the results. If an error occurs, process_query() uses PQerrorMessage() or PQresultErrorMessage() to display an error message to the user. You should call PQerrorMessage() if PQexec() fails to return a PQresult pointer; otherwise, you call PqresultErrorMessage().

If the command is successful, you need to decide whether it was a SELECT or some other type of command. If PQresultStatus() returns PGRES_TUPLES_OK, you know that the command was a query and you can call print_result_set() to do the grunt work. If PQresultStatus() returns PGRES_COMMAND_OK, you know that some other command has executed. PQcmdStatus() tells you the name of the command that just completed. You've probably noticed that when you execute a command (other than SELECT) in psql, the name of the command is echoed if the command succeeded—that's what PQcmdStatus() gives you. PQcmdTuples() tells you how many rows were affected by the command. PQcmdTuples() is meaningful for the INSERT, UPDATE, or DELETE command. For any other command, PQcmdTuples() returns a string of zero length. If you've just executed an INSERT command (that inserted exactly one row), you can retrieve the OID of the new row by calling the PQoidValue() function. Note that some tables may not contain an OID column—in that case, PQoidValue() returns InvalidOid. process_query() finishes by freeing up the PGresult object and all the resources (that is, memory) managed by that object.

The main() function for client3d.c is the same as for client3.c:

117 int main( int argc, char * argv[] )
118 {
119   PGconn * connection;
120
121   if( argc != 2 )
122   {
123     printf( "usage  : %s "connection-string"
", argv[0] );
124     printf( "example: %s "user=myname password=cows"
", argv[0]);
125     exit( 1 );
126   }
127
128   if(( connection = PQconnectdb( argv[1] )) == NULL )
129   {
130     printf( "Fatal error - unable to allocate connection
" );
131     exit( 1 );
132   }
133
134   if( PQstatus( connection ) != CONNECTION_OK )
135     printf( "%s
", PQerrorMessage( connection ));
136   else
137     process_query( connection, "SELECT * FROM rentals" );
138
139   PQfinish( connection );
140
141   exit( 0 );
142 }

Now let's compile this client and run it:

$ make client3d
cc -g -I/usr/local/pg800/include  -c -o client3d.o client3d.c
cc -g  client1.o -L/usr/local/pg800/lib -lpq -o client3
$ ./client3d "dbname=movies"
tape_id  rental_date customer_id
-------- ----------- -----------
AB-12345  2002-07-01           1
AB-67472  2002-07-01           3
OW-41221  2002-07-01           1
(3 rows)

Let's compare that with the output from client3:

$ ./client3 "dbname=movies"
tape_id |rental_date|customer_id
--------+-----------+-----------
AB-12345| 2002-07-01|          1
AB-67472| 2002-07-01|          3
OW-41221| 2002-07-01|          1
(3 rows)

Pretty similar—the only differences are in the vertical separator characters. Remember, client3 uses the PQprint() function (from the libpq library) to format the result set. In client3d, you did all of the hard work yourself.

The Prepare/Execute Model

One of the newer features in PostgreSQL is the prepare/execute model (introduced in version 7.3). When you execute a SELECT command, the PostgreSQL server parses the command, generates a set of possible execution plans, optimizes the query (by selecting the best plan), and then executes the command right away. If you execute the same command a second time, the server goes through the same process of parsing, planning, and optimizing the command. If you execute that command 1,000 times, the server parses, plans, and optimizes the command 1,000 times. It's very likely to come up with the same plan each time.

The prepare/execute model gives you more control over the process. To use the prepare/execute model, you first PREPARE a command, then you EXECUTE the command. If you want to execute the same command over and over again, you skip the PREPARE part and simply EXECUTE the prepared command. When you PREPARE a command, the server parses, plans, and optimizes the command and saves the data structures that it needs in order to execute the command later. The syntax for the PREPARE command is

PREPARE plan-name [ (datatype [, ...] ) ] AS statement
						

Once the server has parsed, planned, and optimizes the statement, it associates the resulting execution plan with the given plan-name. For example:

movies=# PREPARE getCustomerNames AS SELECT customer_name FROM customers;
PREPARE

To execute the getCustomers plan, use the EXECUTE command:

movies=# EXECUTE getCustomerNames;
    customer_name
----------------------
 Panky, Henry
 Jones, Henry
 Wonderland, Alice N.
 Rubin, William
(4 rows)

You can EXECUTE a plan as many times as you like. The server remembers prepared execution plans until your session ends but you can force it to forget a plan with the DEALLOCATE plan-name command (you must DEALLOCATE a plan before you can reuse the name). If you look closely at the syntax for the PREPARE command, you'll notice that you can include a list of (optional) data types after the plan-name. When you PREPARE a command that you intend to reuse, you can include parameter markers in the command and then substitute actual values each time you EXECUTE the command. For example, to PREPARE a parameterized query that expects a customer_id each time you execute it:

movies=# PREPARE getCustomers( INTEGER, INTEGER ) AS
movies-#  SELECT customer_name FROM customers
movies-#    WHERE customer_id >= $1 AND customer_id <= $2;
PREPARE

Notice the $1 and $2 things in the WHERE clause—those are parameter markers. You can include as many parameter markers as you need, but you can only place a parameter marker where PostgreSQL expects to find a value (or an expression). You can't, for example, PREPARE a command such as SELECT * FROM $1, hoping to provide a table name each time you EXECUTE the command. When you PREPARE a parameterized command, you must tell PostgreSQL the approximate data type for each parameter. I say “approximate” because, as long as the value that you provide at EXECUTE—time can be converted into the required type, you'll be OK. Be aware that the query optimizer will often find a better execution plan if you can exactly match parameter types to column types. To execute a parameterized plan, use the EXECUTE command, but include the parameter values, like this:

movies=# EXECUTE getCustomers( 2, 3 );
customer_name
---------------
 Panky, Henry
(1 row)

Of course, if you EXECUTE a prepared command a second time, the server will skip the parsing, planning, and optimizing steps, even if you provide different values for the command parameters. You can only use the prepare/execute model to execute SELECT, INSERT, or UPDATE commands.

There are two ways to use the prepare/execute model in a libpq application. First, you can simply create command strings that PREPARE and EXECUTE and then use one of the typical libpq functions to execute those commands. That method certainly works and you'll often increase performance in those cases where you execute the same commands over and over again. The second method involves using a new set of libpq functions.

To prepare a SELECT, INSERT, or UPDATE command from a libpq application, call the PQprepare() function:

PGresult *PQprepare( PGconn     * connection,
                     const char * planName,
                     const char * queryText,
                     int          typeCount,
                     const Oid  * paramTypes );

The PQprepare() function expects a pointer to a PGconn object, a name for the plan, the text of the query, and an array of parameter data types (along with a count (typeCount) of the number of elements in the paramTypes array). Unlike the PREPARE command, you don't have to declare the type of each parameter in the query. If you don't declare the type of a given parameter, the server infers the type by treating the parameter as an un-typed literal string. If you decide to declare parameter types, the paramTypes parameter should point to an array of typeCount OID values. Each element in the paramTypes array corresponds to a parameter (paramTypes[0] declares the data type for parameter $1, paramTypes[1] declares the data type for parameter $2, and so on). So what do you put in the paramTypes array? The OID (object id) of the pg_class entry for the server-side data type. For example, if you want to declare the first parameter as an int4, you would set paramTypes[0] = 23. To find the correct OID value, use the query: SELECT oid FROM pg_type WHERE typnam = 'type' (that's not a typo—there's only one 'e' in typname). If you prefer, you can find symbolic names for the predefined data types (that is, the data types included in a standard PostgreSQL distribution) in the postgresql/server/catalog/pg_type.h header file. For example, the pg_type.h header defines the symbol:

#define INT4OID  23

Here's a code snippet that prepares the getCustomers query you saw earlier:

...

Oid paramTypes[2] = { 23, 23 };

result = PQprepare( connection,
                    "getCustomers",
                    "SELECT * FROM customers WHERE customer_id BETWEEN $1 AND $2"
                    2,
                    paramTypes );
...

If PQprepare() returns successfully, the server has created an execution plan named getCustomers. The result object that you get back from PQprepare() will tell you whether the PREPARE succeeded or failed, but it (obviously) won't contain the result set from the SELECT command. Remember that you don't have to provide types for each command parameter. The fourth argument (typeCount) to PQprepare() specifies how many OIDs are present in the paramTypes array, not the number of parameters in the command. If typeCount is less than the number of parameters in the prepared command, the server infers the type of each remaining parameter. If you want to declare the data types for trailing parameters and skip some of the leading parameters, just set the missing data types to InvalidOid, like this:

Oid paramTypes[3] = { InvalidOid, InvalidOid, 23 };

Once you've prepared a command, you can execute that command by calling the PQexecPrepared() function:

PGresult *PQexecPrepared( PGconn             * connection,
                          const char         * planName,
                          int                  paramCount,
                          const char * const * paramValues,
                          const int          * paramLengths,
                          const int          * paramFormats,
                          int                  resultFormat );

The meaning of the first two arguments should be obvious: connection points to a PGconn connection object and planName points to a null-terminated string containing the name of the command that you prepared earlier. The last five arguments take a bit of explanation.

I'll start with the last argument because it's the easiest to explain. If resultFormat is 0, libpq returns each value in the result set in the form of a null-terminated string. That's typically what you find in a libpq application. If you retrieve a numeric value from the server, you'll get the result as a null-terminated string that you must convert into binary form (say an int or float) using a function such atoi() or strtof(). If you retrieve a date value, you'll get a null-terminated string (and you'll have to convert it to the binary form you require). If resultFormat is 1, libpq returns every value in the result set in binary form. What exactly is binary form? It varies with the server-side data type. If you retrieve an int4 value in binary form, you'll get back a four-byte value that looks suspiciously like an int. In fact, on some systems, an int4 value is exactly the same as an int value, but only if the byte-ordering of your CPU matches “network” byte ordering. The PostgreSQL server transfers integer values in network byte order as defined by the ntohs(), ntohl(), htons(), and htonl() functions. In a multibyte network-ordered value, the first byte transferred across a network (or the byte with the lowest address in memory) is the most significant byte. Most RISC processors (such as Sun's SPARC and HP's PA-RISC) store multibyte integer values in network byte order. Network byte ordering is also known as big-endian or RISC ordering. Intel processors use little-endian ordering (or simply, Intel ordering). If your client application is running on a little-endian CPU, you must convert integer values from network order to host order. That's what the ntohs() (network-to-host short) and ntohl() (network-to-host long) functions do. Table 8.3 shows the relationships between server data types and their corresponding C data types.

^[*] (Source: PostgreSQL Documentation, Section 31.7.2)

What Table 8.3 does not tell you is how to convert a binary value as delivered to your client application into the proper byte ordering. In fact, PostgreSQL doesn't document which pieces are stored in network order and which are stored in host order. To find that information, you have to look at the type_recv() and type_send() functions in the PostgreSQL source code. For example, to find the network format of a DATE field (stored in a DateADT), you'd have to read through the date_recv() and date_send() functions. And since the format is officially undocumented, it may change with the next release.

We can't recommend using binary format for two reasons. First, it's difficult to find the exact binary representation of each value and the representation may change from release to release. Second, the primary benefit of binary format values is performance—if a value arrives in your application in the format you need, you don't have to convert it from string form first. However, because binary values are not guaranteed to be stored in client-host order, you'll have to convert them anyway (unless you happen to be running the client application on a RISC cpu). In general, binary values are rarely worth the hassle.

Now back to the PQexecPrepared() function. As we mentioned before this short diversion, the last argument to PQexecPrepared() determines whether the query results are returned in string form or in binary form. The paramFormats argument tells libpq whether you've provided the parameter values (the values that get substituted into the query) in string form or binary form. The paramFormats argument points to an array of (int) indicators—each indicator corresponds to one of the parameter values pointed to by the paramValues array. paramFormats[0] determines the format of paramValues[0] (which corresponds to $1), paramFormats[1] determines the format of paramValues[1] (which corresponds to $2), and so on. If paramFormats[n] is 0, paramValues[n] must point to a null-terminated string. If paramFormats[n] is 1, paramValues[n] must point to a value in binary format. Notice that paramValues[n] must point to a value in either case—it never contains a data value, just a pointer to a value. The rules for supplying binary parameter values are the same as the rules for receiving binary values in the result set—the data type of the binary value must match the network-ordered format of the server-side data type. Again, we don't recommend using binary parameter values—they are error-prone and subject to change.

At this point, the purpose of each argument should be clear. paramCount determines how many entries are in the paramValues, paramLengths, and paramFormats arrays. Each element in paramValues points to a parameter value. paramValues[0] points to the value for parameter $1, paramValues[1] points to the value for parameter $2, and so on. If paramValues[n] contains a null pointer, then parameter $n+1 is assumed to be NULL (likewise, if paramCount is less than the number of parameters in the prepared command, the remaining parameters are assumed to be NULL). If you are providing parameter values in string form, paramLengths and paramFormats should both be null pointers. If you are providing (at least some) parameters in binary form, paramFormats should point to an array of (int) indicators (as described earlier) and paramLengths should point to an array of paramCount lengths (also of type int). If you are providing parameter $n+1 in binary format, paramFormats[n] should set be set to 1 and paramLengths[n] should contain the length of the binary value of the parameter.

Here's a code snippet that shows how to execute the getCustomers command (described earlier) using string-form parameters:

...

const char * values[2];

values[0] = "2";     // Start with customer number 2
values[1] = "3";     // End with customer number 3
result = PQexecPrepared( connection,       // connection object
                         "getCustomers",   // planName
                         2,                // paramCount
                         values,           // paramValues
                         NULL,             // paramLengths
                         NULL,             // paramFormats
                         0 );              // resultFormat

PostgreSQL assumes that each parameter value is provided in string form because the paramFormats argument is NULL. The following snippet shows how to execute the getCustomers command using binary-form parameters:

...
int4      firstCustomer = (int4) htonl( 2 );  // Start with customer number 2
int4      lastCustomer  = (int4) htonl( 3 );  // End with customer number 3
int4   *  valuePointers[2];
int       valueLengths[2];
int       valueFormats[2];

valuePointers[0] = &firstCustomer;
valuePointers[1] = &lastCustomer;

valueLengths[0] = sizeof( firstCustomer );
valueLengths[1] = sizeof( lastCustomer );

valueFormats[0] = 1;                      // Indicate that $1 is in binary format
valueFormats[1] = 1;              // Ditto for $2

result = PQexecPrepared( connection,       // connection object
                         "getCustomers",   // planName
                         2,                // paramCount
                         valuePointers,    // paramValues
                         valueLengths,     // paramLengths
                         valueFormats,     // paramFormats
                         0 );              // resultFormat

As you can see, providing parameter values in binary form is much more complex. Don't forget to convert multibyte integer values into network order first or you'll be sending the wrong values to the server.

libpq provides another function that combines the PREPARE and EXECUTE steps into a single function: PQexecParams().

PGresult *PQexecParams(PGconn             * connection,
                       const char         * command,
                       int                  paramCount,
                       const Oid          * paramTypes,
                       const char * const * paramValues,
                       const int          * paramLengths,
                       const int          * paramFormats,
                       int                  resultFormat )

Notice that this function combines the arguments of the PQprepare() and PQexecPrepared() functions and the arguments work the same way. You may be wondering why libpq provides this function if the whole point behind the PREPARE/EXECUTE model is to separate the two steps. PREPARE/EXECUTE can improve performance if you execute the same command over and over again, but it can also simplify your client application. In most client applications, you execute a fixed set of commands and you know what those commands will be at the time you write the application. What you generally don't know at the time you write a client application is the exact set of values that you'll be working with when you run your program. For example, if you're writing a program that you will use to add tapes to the movies database, you know what a typical INSERT command will look like:

INSERT INTO tapes( tape_id, title, duration )
  VALUES( 'MC-68873', 'The Godfather', NULL );

but you don't know the actual data values. If you were writing a libpq application using an older version of PostgreSQL (older than 7.3), you had to generate the entire INSERT command each time you wanted to add a new row. Before the PREPARE/EXECUTE model, you would typically generate the INSERT command using the sprintf() function, like this:

...
char      insertCommand[MAX_COMMAND_LEN+1];

sprintf( insertCommand,
         "INSERT INTO tapes(tape_id,title,duration) VALUES('%s', '%s', '%s')",
         tapeID, title, duration );

At first glance, the sprintf() approach doesn't seem too bad, but there are a number of gotchas lurking in that code. First, what happens if the formatted command won't fit into the insertCommand buffer? That's a stack overflow waiting to happen (of course you can avoid the stack overflow by using the snprintf() function instead, but that merely truncates the command before it overflows—you still end up with an invalid command). Next, you have to take care of NULL values in some fashion. For example, if the duration value is unknown, you should really insert NULL instead of 0. That makes the code more complex. Finally, any string values (like the tapeID and title values) must be properly quoted. What happens if you try to add a tape whose title is “The Sorcerer's Apprentice”? That apostrophe will result in a syntax error unless you take steps to quote it properly first. Finally, if you've gathered any of these values from an untrusted source, a devious user could destroy data simply by supplying a carefully crafted movie title. Consider what would happen if you blindly inserted a movie titled "Bye Bye Data', '5 hours' ); DROP DATABASE movies;" into the previous command.

You can avoid all of those problems using PREPARE/EXECUTE. Even if you don't need to execute the same command over and over again, you can still use PQexecParams() to simplify your application.