stored procedures in postgresql via libqp   (geekin, random re me)

This entry will have an astronomical geek index, so if you have no idea what the title means, you should probably just move along, nothing to see here.

This is a work in progress, and will potentially have a sequel.

Let's outline the problem at hand. There is a postgresql database. You'd like to connect to it. You've decided to implement in C, and therefore you will be using libpq to connect to your database. Connecting isn't really that difficult, but as has been discovered by me and my more-technical-than-usual project manager, there is not much documentation at all to be had on the specifics of postgresql stored procedures and using them in conjunction with libpq.

So I'll assume the reader is competent enough to get the postgresql libraries installed. That leaves us with how, exactly, to get data into and back out of the database. There are a couple classes of stored procedures, some of which can be handled trivially, and others which can't. Here's a trivial case:

/* so you have PGresult *r,

 * which is the result of calling a stored procedure

 * that returns a single integer. */

if (PQresultStatus(r) == PQ_TUPLES_OK) {

    if (PQntuples(r)) {

        fprintf(stderr, "got [%d]\n", PQgetvalue(r, 0, 0));

    }

}