Version française
Home     About     Download     Resources     Contact us    

This site is updated infrequently. For up-to-date information, please visit the new OCaml website at

Browse thread
Type-safe interface to Postgres's SQL
[ Home ] [ Index: by date | by threads ]
[ Search: ]

[ Message by date: previous | next ] [ Message in thread: previous | next ] [ Thread: previous | next ]
Date: 2006-02-18 (16:01)
From: Richard Jones <rich@a...>
Subject: Re: [Caml-list] Type-safe interface to Postgres's SQL
On Tue, Jan 31, 2006 at 02:07:33PM +0000, Richard Jones wrote:
> (2) PostgreSQL doesn't track NULL types properly so we have to do a
> bit of non-trivial guesswork to try to find out if a database field
> could contain a NULL or not (and therefore whether to map its type to
> 'a or 'a option).  For parameters this isn't possible at all, so all
> parameter variables must have an option type.  For result fields, it
> is usually possible to tell if the result corresponds to a known
> column in the database, which is the case in ninety percent of
> queries.

FWIW I have solved this problem with a small syntactic change.  You
now use $varname when you don't want NULLs and $?varname when you
do.[1] A modified example program is attached.

You can also download the full program from here:


[1] It is still possible to insert a NULL into a NOT NULL field, as
was the case before, but I can't see any way to solve that without
rewriting Postgres.

(* Example program using typesafe calls to PostgreSQL.
 * $Id:,v 1.4 2006/02/18 15:54:06 rich Exp $

open Printf

let () =
  let dbh = PGOCaml.connect () in

  PGSQL_EXECUTE_ON_COMPILE(dbh) "create temporary table employees (
     id serial not null primary key,
     name text not null,
     salary int4 not null,
     email text

  let insert name salary email =
    PGSQL(dbh) "insert into employees (name, salary, email)
                values ($name, $salary, $?email)"
  insert "Ann" 10_000_l None;
  insert "Bob" 45_000_l None;
  insert "Jim" 20_000_l None;
  insert "Mary" 30_000_l (Some "");

  let rows = PGSQL(dbh) "select id, name, salary, email from employees" in
  List.iter (
    fun (id, name, salary, email) ->
      let email = match email with Some email -> email | None -> "-" in
      printf "%ld %S %ld %S\n" id name salary email
  ) rows;

  PGOCaml.close dbh

Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology -
Team Notepad - intranets and extranets for business -