Beginning Databases with PostgreSQL

Accessing PostgreSQL from Perl (S. 465-466)

As earlier chapters have shown, communicating with PostgreSQL generally involves a lot of string manipulation. One language that excels at string manipulation is Perl. In Chapter 13, we demonstrated that the libpq interface is a powerful way to access a PostgreSQL database, but there are disadvantages. We need to use string manipulation to pass values to queries and to retrieve results, and for short programs, the C code dealing with strings can overshadow the database interactions.

As Chapter 13 pointed out, although binary access is possible, its benefits are minimal. With Perl, strings are much more sophisticated, supporting functionality such as joining, splitting, pattern matching, and automatic conversion to and from other data types. Perl has also historically been associated with web server processing (although more modern mechanisms such as PHP, described in the previous chapter, are taking over that role).

Having interfaces to databases definitely adds benefits. If you know even a little about Perl, you will be aware that one of the language’s axioms is that there is always more than one way to tackle any given job. In fact, Perl enthusiasts would be disappointed if they had to limit their options to single figures. We do not propose to bombard you with numerous techniques for accessing PostgreSQL databases from Perl, however. Instead, we will present a single methodology. There are essentially three ways to access PostgreSQL from Perl:

• Low-level access, which is essentially a Perl mapping of the libpq C interface (Module Pg)
• High-level access, using a database independent layer (DBI)
• Access by embedding the Perl interpreter (similar to the description in Chapter 14)

We will describe only the high-level DBI access mechanism, because it is the simplest to install and use. This method is database-independent, yet is still very flexible and powerful. If you are interested in a libpq-style of working, we suggest taking a look at Module Pg, which is part of the DBD:: Pg database driver. PL/Perl requires a version of Perl to have been initially built as a shared library - libperl.so, as opposed to the more usual libperl.a (see the instructions for building Perl found in Perl source distributions).

Installing Perl Modules

The Perl programming language supports the concept of modules - additional functions that can be integrated into a Perl installation to provide extra features. Many developers have developed modules to extend Perl, providing diverse functionality, including network protocols for file transfer or sending e-mail, parsing and manipulating XML documents, generating graphical images, and more. Much of Perl’s standard functionality is provided by modules included with a basic Perl installation.

In this chapter, we will be using several modules that are not included in the base Perl installation. Because installing modules is a fairly common task, the Perl community has developed a standard process for finding and installing modules. This process has itself been encapsulated in a Perl module, the CPAN module, which we will meet in a moment. Perl modules can be written in several languages, not just Perl. Modules written in a language other than Perl need to be compiled before they can be installed, but binaries are often available to download.