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 http://common-lisp.net/project/postmodern/

Drupal is a very nice content management system which supports PostgreSQL. You can find a Drupal/PostgeSQL discussion group at http://groups.drupal.org/postgresql.

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.

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:

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.

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.

It has been a somewhat embarrassing secret. Until this weekend, pgedit.com 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, pgedit.com 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.

I ran across Planet PostgreSQL a month or so ago. At the time it did not have much content, but now some of the PostgreSQL movers and shakers have started blogging there. Definitely worth adding to your RSS feeds.

A preview of coming attractions in pgEdit 1.1: PHP support. PHP can be used in two different ways. First, it can be used to write plPHP stored procedures. In this case pgEdit looks for a $php$ dollar quote or the LANGUAGE parameter with 'plPHP' at the start of the function definition. If this is found, PHP syntax coloring is used for the body of the function.

PHP can also be used as a preprocessor to parameterize and dynamically generate SQL. pgEdit recognizes opening and closing PHP tags and automatically switches syntax coloring between PHP and SQL. The PHP output from the mixed syntax file is then passed to psql for processing.

The PostgreSQL Tools for Mac OS X project has been started on SourceForge. The goal of the project is to bring better PostgreSQL installation and management tools to OS X.