John DeSoi's blog

PostgreSQL motorcycle in Austin, Texas

I came across this motorcycle leaving the movies on Friday. Very cool!

Drupal 7 upgrade

Wow, 2 posts in one year. I'll try to be less chatty.

I finally upgraded this web site to Drupal 7. One big advantage of Drupal 7 over previous versions is a database abstraction layer based on PHP PDO. This means that all Drupal 7 modules should support PostgreSQL without any additional changes. In previous versions, PostgreSQL module support lagged far behind because developers often coded only for MySQL. Another advantage is Drupal 7 now supports more databases. PostgreSQL, SQLite, and MySQL are officially supported, and any database that supports PDO should theoretically work.

PostgreSQL Lisp library

Postmodern is a Common Lisp library for interacting with PostgreSQL. It uses the frontend/backend protocol to communicate directly with PostgreSQL over a TCP/IP socket. Features include:

  • Efficient communication with the database server without need for foreign libraries.
  • Support for UTF-8 on Unicode-aware Lisp implementations.
  • A lispy syntax for expressing SQL queries.
  • Convenient support for prepared statements and stored procedures.
  • Defclass-like definition of tables and associated accessor classes.

For more details and downloads see

Drupal group for PostgreSQL

Drupal is a very nice content management system which supports PostgreSQL. You can find a Drupal/PostgeSQL discussion group at

More fun with definitions

The definition parsing features added in pgEdit 1.2 are great, but only useful for open files. And somehow the definition I'm looking for always seems to be hidden away in some file I have not opened in a while. So the next version of pgEdit will have a spiffy new definitions browser that can search for definitions in open files as well as file system directories (see screen shots below). You can also choose to search your list of recently opened files.

The browser consists of two views: one for specifying search options, the other for previewing files. Clicking on a definition in the result list switches to the editor view and highlights the definition. Full editing capabilities are available here, so this provides a convenient way to quickly change a number of related definitions. Double-clicking on the definition opens the file in the full editor window.

Name parsing with PL/Perl

I have been working on a database cleanup task which required normalizing names of people into first and last names. Unfortunately, this is legacy data from many different databases where a single field is used for the entire name. The names appear in every conceivable order and variation, with lots of extraneous words and characters thrown in for extra fun.

Certainly I was not the first person faced with this task, so I searched for existing tools rather than start from scratch. I found commercial software to do this "starting at" $10,000. I hunted around a bit more and came across this Perl module for parsing names.

I have never written a single line of Perl in my life, but I thought I would give it a try. I was able to get the module installed via CPAN on OS X without too much trouble.

Next, I wanted to get it running in PostgreSQL so I could call the name parser in the database. I recompiled PostgreSQL using the --with-perl option and then installed PL/Perl in my database using the CREATE LANGUAGE command. I created a PL/Perl function based on examples from the module and was pleasantly surprised when everything worked on the first try. Here is the function I ended up with:

Definition Parsing

A short blurb about the definition parsing features coming in the next version of pgEdit. There are two new commands so far, with lots more added to the wish list. I have been using these features regularly for the past few weeks and find they save me a great deal of time. I hope to make them available in a beta version sometime in the next few weeks.

  • Find Definition - Finds the definition source by name (with completion) in any open file. Jumps directly to the definition without prompting if the highlighted text matches the name of a definition.
  • List Buffer Definitions - List all of the definitions in the file with an icon corresponding to the definition type. Full keyboard control for type-ahead selection, jump to definition (Return), or cancel (Escape).

Here is a screenshot for Macintosh and one for Windows.

PostgreSQL Plugin for 4D

Pluggers Software has produced a very nice PostgreSQL plugin for 4th Dimension. It is based on libpq, so you'll find all the typical features you expect in a PostgreSQL client library including prepared statements, LISTEN/NOTIFY handling, and data type conversion. The library also includes handy row set management routines with calls similar to those used in 4D selection handling.

After evaluating the demo version for several days, I sent in my order along with some suggested improvements for handling NULL values in 4D. Shortly after my license arrived, I received a second email with a new version of the plugin attached. It included the features I had just requested.

PostgreSQL Powered

It has been a somewhat embarrassing secret. Until this weekend, was using MySQL for the content management system back end database. I intended to use PostgreSQL from the start, but the hosting provider only offered an old version (7.3) and there was a lack of necessary tools to run it properly. MySQL had an automatic installer that set everything up with a few mouse clicks.

Happily, is now running the latest version of Drupal and PostgreSQL 8. In addition to being a very nice content management system, Drupal provides a flexible framework for developing web database applications. There was a recent thread about this on the pgsql-general mailing list. Kevin Murphy pointed out the existence of some MySQL bias in the Drupal contributed modules and suggests the idea of a pool of Drupal/PostgreSQL testers to remedy the situation.


Subscribe to RSS - John DeSoi's blog