SQL Perl/PL Overview

Procedural languages provide the fundamental method for programming within an SQL database. In other articles in this series, we have covered the SQL-based procedural languages, but those are generally not the only procedural languages found in an SQL database. Procedural extensions for Perl and Java tend to be the most popular additions, but some database systems implement many more additional languages.

The following examples make use of PostgreSQL’s implementation of PL/Perl, but the examples should be relevant and similar to other procedural language extensions in most databases that support such additions.

Installing and Enabling PL/Perl

In an operating system that is bundled with numerous packages, like Red Hat Linux, the PL/Perl language should be available for immediate use. In other operating systems, it may need to be downloaded and installed as a package from that operating system’s package repository.

Debian Linux, for example, does not include PL/Perl in the base PostgreSQL package, and it must be downloaded and installed separately. In Debian and other APT-based Linux distributions, like Ubuntu, the command to do this for PostgreSQL 8.1 is apt-get install postgresql-plperl-8.1. The postgresql-plperl-8.1 package may also be installed via a package manager like dselect or aptitude. As of this writing, November, 2007, PL/Perl is available in Debian’s APT package repositories for the testing (etch) distribution for PostgreSQL versions 8.1 and 8.2.

Once PL/Perl is installed on the computer, it must be enabled for each database that will use PL/Perl functions. This may be accomplished by executing the CREATE LANGUAGE plperl command in the psql SQL shell.

“Hello, World!”

Naturally, the first task one should complete is the creation of the simple “Hello, World!” function. Figure 1 shows the creation and execution of this function.

The only real difference between this function and a procedural SQL “Hello, World!” function is that it is written in Perl, instead of SQL. As long as the developer knows how to program in Perl, writing PL/Perl code is quite simple. Also note the LANGUAGE plperl clause, which lets PostgreSQL know the function is written in PL/Perl. In Figure 1, we simply returned the “Hello, World!” string, and it was displayed in the result set of the SELECT hello_world() query.

Concatenation

Another simple example, the concatenate() function, is shown in Figure 2.

Figure 2 shows the creation of concatenate(), its use as a function, and its use as both a function and as a parameter passed to itself. In the function, the two TEXT parameters are shifted out of the parameter list, and Perl’s . operator is used to concatenate them. As Perl developers are wont to say, “there is more than one way to do it,” and that is certainly the case with a function like this. Another popular way of writing this function is shown in Figure 3.

SQL Perl/PL Overview