PostgreSQL Functions and libpq, round 2

OK, so I realized my previous post didn't show an example of inserting data into the database. Why are these functions of interest? In my workplace, we have a strict policy that all programmatic access to the database is via accounts that have only execute permission. This means the accounts these programs use to access the database are never allowed to read or write the database directly. The database development team here does a great job of checking that the inputs to the database are valid and reasonable before storing data, and this gives us complete confidence that stupid SQL inject attacks cannot work.
So, without further ado, here is the function to insert a customer into our simple database. I'll include the database too, since it's been a while since the original post.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    firstname TEXT,
    lastname TEXT
);

COPY customers (id, firstname, lastname) FROM stdin;
1     John     Smith
2     Jay      Johnson
3     Jason    Crawford
4     Jeph     Jacques

\.

CREATE OR REPLACE FUNCTION loadCustomer

    (IN customers.firstname%TYPE,

     in customers.lastname%TYPE)

    RETURNS INTEGER

AS $$

DECLARE

    customerFirst   ALIAS FOR $1;

    customerLast    ALIAS FOR $2;

BEGIN

    INSERT INTO customers (firstname, lastname)

    VALUES (customerFirst, customerLast);

    return 1;

END;

$$ LANGUAGE 'plpgsql';

You'll notice that our function doesn't touch the id column.  That's because we want the database to maintain this as a unique value for us.  To do this, we create a sequence and tell the database to use the sequence as the default value for the column.  Since we're hacking this into our database after the fact, the following DDL will get us what we're looking for:

wes=# SELECT max(id) FROM customers;
 max
-----
   4
(1 row)

So that's the number we need to start after.

CREATE SEQUENCE customer_id_seq START 5;

Now we alter the customers table to use this as the default:

ALTER TABLE customers

ALTER COLUMN id

SET DEFAULT nextval('customer_id_seq');

Postgres will now maintain the customers.id for us.  Now that we've got a database and function, let's call it from C.  Here's the program text, you'll notice it's quite similar to the original program:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    PGconn      *conn;
    PGresult    *res;
    const char  *paramValues[2];
    char        *val;

    // Argument is the connection info, if specified.

    const char *conninfo = (argc > 1) ? argv[1] : "dbname=wes";

    // Make a connection to the database
    conn = PQconnectdb(conninfo);
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",

                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    // The parameter value array is used to store a row of values to be 

    // passed as args to the function.  We need 2, since that's how big

    // our function arg list is.

    paramValues[0] = "Tom";
    paramValues[1] = "Jefferson";

    res = PQexecParams(conn,
                       "SELECT * FROM loadCustomer($1, $2)",
                       2,           // two params per call
                       NULL,        // let the backend deduce param type
                       paramValues,
                       NULL,        // don't need param lengths since text
                       NULL,        // default to all text params
                       0);          // ask for text results
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

     // The return from our function should be the single value 1.

    val = PQgetvalue(res, 0, 0);
    printf("PG said: %s\n", val);


    // Close the connection to the database and cleanup.
    PQclear(res);
    PQfinish(conn);
    return 0;
}

And sure enough, it runs correctly:

[wes@eeegor ~/src/PG]$ cc -g -I/usr/local/include -L/usr/local/lib insert.c -lpq
[wes@eeegor ~/src/PG]$ ./a.out
PG said: 1

[wes@eeegor ~/src/PG]$ psql wes
psql (8.4.0)
Type "help" for help.

wes=# select * from customers;
 id | firstname | lastname
----+-----------+-----------
  1 | John      | Smith
  2 | Jay       | Johnson
  3 | Jason     | Crawford
  4 | Jeph      | Jacques
  5 | Tom       | Jefferson
(5 rows)

This actually compiled and ran for me on the first try.  Sure, it's a trivial change from the previous program, but that's not usually my experience.