The PostgreSQL documentation states that PQexecParams can be called like so:

PGresult *PQexecParams(PGconn *conn,
const char *command,
int nParams,
const Oid *paramTypes,
const char * const *paramValues,
const int *paramLengths,
const int *paramFormats,
int resultFormat);

There are two parts of this call which can be tricky to novice C and/or PostgreSQL users and aren’t explained very well in the documentation. You may be asking yourself “what the hell is a const char * const * value and how do I make one?” You may also be asking “What is a const Oid?”

A const char * const * is a constant array of constant pointers to char pointers. In English, this means that you cannot change what the pointers are pointing to, and you cannot change any of the values (ie: a read-only array of char*s). We can create a const char** variable and pass that to the function, as shown below.

A const Oid variable is a constant reference to the PostgreSQL Object ID type. This type is an unsigned int data type which is used by PostgreSQL’s internal code to reference primary keys on system tables (see this page for details). If we pass a NULL value for this argument, PostgreSQL will figure it out for itself. Nice.

So let’s see an example. Imagine we have a table called person with an INT field person_id and a VARCHAR field name. Below is an example of querying the table using both binary and text parameters.

We want to search for people whose name is “John” and whose person_id is not 101. Assume that we have a connection handle, stored in conn.

//we need to convert the number into network byte order
int person_id = htonl(101);
char *name = "John";
//set the values to use
const char *values[2] = {(char *)&person_id, name};
//calculate the lengths of each of the values
int lengths[2] = {sizeof(person_id), strlen(name)};
//state which parameters are binary
int binary[2] = {1, 0};

PGresult *res = PQexecParams(conn,
"SELECT person_id, name FROM person WHERE person_id != $1::int4 and name = $2::varchar",
2, //number of parameters
NULL, //ignore the Oid field
values, //values to substitute $1 and $2
lengths, //the lengths, in bytes, of each of the parameter values
binary, //whether the values are binary or not
0); //we want the result in text format

You can now treat res like any other PGresult.

  1. Robb says:

    Thank you very much for this post! I struggled about this strange array.

  2. Finally says:

    Finally one workable example of PQexecParams.
    Thank you very much.

  3. dake says:

    Thank you, helpful!

  4. Muhammad Jam says:

    Really impressed. Thank for this post..Keep on..