The Hungry Hacker's Explanation of Everything

Home » Code

PostgreSQL libpq Programming Example

21 September 2005 No Comment

Update: After years of sitting idle, and moving across about three or four different CMS systems, almost all of which decided to arbitrarily munge various portions of this code, I honestly have no idea if it works or not. I’d suggest giving it a shot, and consulting the manual frequently. My apologies for WordPress eating the white space, there’s not really much I can do about it.

Honestly, the programming examples on the official PostgreSQL website aren’t that bad. I just thought that I could write my own, and show people how I like to use the libpq API in C (and for Google purposes, Cee).

Initializing and Connecting

The first thing you’ll need is the libpq-fe.h header, to allow you to use all the goodies that libpq has to offer:

#include libpq-fe.h

Connecting to the database server is done via the PQconnectdb() function, with the configuration being passed as a string. Of course, this is a terribly nasty way of doing things in my humble opinion, but I’m sure there are alternatives if you look for them.

PGconn *psql;
psql = PQconnectdb("hostaddr = '127.0.0.1' port = '' dbname = 'fwaggle' user = 'fwaggle' password = 'password' connect_timeout = '10'");
/* init connection */
if (!psql) {
fprintf(stderr, "libpq error: PQconnectdb returned NULL.\n\n");
exit(0);
}
if (PQstatus(psql) != CONNECTION_OK) {
fprintf(stderr, "libpq error: PQstatus(psql) != CONNECTION_OK\n\n");
exit(0);
}

As you can see, everything’s done for you with a single function call, and we simply check to see if we’re connected by 1) checking if the pointer was NULL and 2) checking PQstatus()‘s return value. If you’re not writing CGI and you’re writing a long-term daemon of some sort, you probably want to include multiple PQstatus() calls in your application.

Cleanup

I wouldn’t be a good little hacker if I didn’t show you how to cleanup after yourself, would I? Thankfully, it’s trivial:

PQfinish(psql);

Queries

Queries aren’t handled the greatest with libpq, so I much prefer to write my own wrapper function for them using variable argument lists (va_arg) if it’s available:

PGresult *pq_query(const char *format, ...) {
va_list argv;
char *ptrQuery;
PGresult *result;
va_start(argv, format);
vasprintf(ptrQuery, format, argv);
va_end(argv);
if (!ptrQuery)
return(0);
result = PQexec(psql, ptrQuery);
free(ptrQuery);
return(result);
}

Then, it’s simply a matter of calling pq_query() with sprintf() style arguments:

#define WORD "this is a test"
PGresult *result;
result = db_query("SELECT now(), md5('%s');", WORD);

Doing something with the data set that result will (hopefully) point to generally requires some kind of loop, unless you’re only expecting a single row. Even though the above SQL will only ever return one row, we’ll still use a loop just in case. In this case, we’re introducing the PQntuples() function, which returns the number of tuples (rows) for a given result set.

I’m not sure if it’s necessary (RTFM, as they say) but I like to check for a NULL result set anyway. We’ll also introduce the PQgetvalue() function, for grabbing values from rows:

int i, j;
if (!result || !(j = PQntuples(result))) {
fprintf(stderr, "libpq error: no rows returned or bad result set\n\n");
PQfinish(psql);
exit(0);
}
for (i = 0; i j; i++) {
printf("Time: %s\n", PQgetvalue(result, i, 0);
printf("MD5: %s\n", PQgetvalue(result, i, 1);
}
PQclear(result);

In case you didn’t figure it out, the arguments for PQgetvalue() are: “result set”, “row”, “column”. Everything starts at 0.

Sanitizing Data

When dealing with any kind of data that comes from user land – whether it be user input, environment variables, whatever – it’s always a good idea to sanitize the data to ensure no one meddles with your SQL queries. libpq provides the PQescapeString() function, but it’s invocation isn’t the cleanest, so again, I like to wrap it in a wrapper function and toast lightly:

char * pq_escape (char *input) {
int len;
char *output;
len = strlen(input);
if (len 1)
return(NULL);
output = malloc((len * 2) + 1);
if (!output)
return(NULL);
PQescapeString(output, input, len);
return(output);
}

Of course, the return value is a pointer to a dynamically allocated variable, which you’ll need to free on your own when you’re done with it. But nonetheless, we can use this wrapper function to easily escape user-provided data:

char *data_safe;
if (argc > 1) {
data_safe = pq_escape(argv[1]);
result = pq_query("SELECT MD5('%s');", data_safe);
if (!result || PQntuples(result) 1) {
fprintf(stderr, "libpq error: no results returned or NULL resultset pointer.\n\n");
PQfinish(psql);
exit(0);
}
printf("data: %s (data safe: %s)\n", argv[0]);
printf("MD5: %s\n", PQgetvalue(result, 0, 0));
PQclear(result);
}

Putting it all together:

Putting the whole mess together, you should have a fully compilable, executable program that takes next to no time to execute and does a couple of measly features:

/*
** libpq example
** fwaggle@hungryhacker.com
** http://www.hungryhacker.com/
*/
#include stdio.h
#include stdarg.h
#include libpq-fe.h
&nsbp;
#define WORD "this is a test"
PGconn *psql;
&nsbp;
PGresult *pq_query(const char *format, ...)
{
va_list argv;
char *ptrQuery;
PGresult *result;
va_start(argv, format);
vasprintf(ptrQuery, format, argv);
va_end(argv);
if (!ptrQuery)
return(0);
result = PQexec(psql, ptrQuery);
free(ptrQuery);
return(result);
}
&nsbp;
char * pq_escape (char *input)
{
int len;
char *output;
len = strlen(input);
if (len 1)
return(NULL);
output = malloc((len * 2) + 1);
if (!output)
return(NULL);
PQescapeString(output, input, len);
return(output);
}
&nsbp;
int main (int argc, char **argv)
{
char *data_safe;
int i, j;
PGresult *result;
psql = PQconnectdb("hostaddr = '127.0.0.1' port = '' dbname = 'fwaggle' user = 'fwaggle' password = 'password' connect_timeout = '10'");
&nsbp;
/* init connection */
if (!psql)
{
fprintf(stderr, "libpq error: PQconnectdb returned NULL.\n\n");
exit(0);
}
if (PQstatus(psql) != CONNECTION_OK)
{
fprintf(stderr, "libpq error: PQstatus(psql) != CONNECTION_OK\n\n");
exit(0);
}
result = pq_query("SELECT now(), md5('%s');", WORD);
if (!result || !(j = PQntuples(result)))
{
fprintf(stderr, "libpq error: no rows returned or bad result set\n\n");
PQfinish(psql);
exit(0);
}
for (i = 0; i j; i++)
{
printf("Time: %s\n", PQgetvalue(result, i, 0));
printf("MD5: %s\n", PQgetvalue(result, i, 1));
}
PQclear(result);
if (argc > 1)
{
data_safe = pq_escape(argv[1]);
result = pq_query("SELECT MD5('%s');", data_safe);
if (!result || PQntuples(result) 1)
{
fprintf(stderr, "libpq error: no results returned or NULL resultset pointer.\n\n");
PQfinish(psql);
exit(0);
}
printf("data: %s (data safe: %s)\n", argv[0]);
printf("MD5: %s\n", PQgetvalue(result, 0, 0));
PQclear(result);
}
PQfinish(psql);
}

Compiling

On my system, I have to play some Makefile games to get libpq to compile in, I’ve seen some Redhat systems that are worse though. Basically, find where libpq-fe.h and the libpq libraries are, and modify your gcc command to suit:

fwaggle@diglett:~$ gcc -I/usr/local/include -L/usr/local/lib -o test_psql -lpq test_psql.c

If it doesn’t work, and barfs on PQconnectdb(), try leaving the hostaddr blank, as that will often cause libpq to connect via a UNIX domain socket, as opposed to TCP/IP (in case your PostgreSQL server isn’t listening on a TCP/IP port). That worked for me:

fwaggle@diglett:~$ ./test_psql Time: 2005-09-21 02:00:28.213502+01 MD5: 54b0c58c7ce9f2a8b551351102ee0938 fwaggle@diglett:~$

Enjoy, and if you have any questions, leave a comment.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar. Note: By filling out this comment form or emailing us you are signifying that you have read and agree to the terms laid out on the Contact Us page.