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.

{qbapagebreak title=Interacting With a Table}

Interacting With a Table

We will start with a table called names, which contains 35 fictional people with unspecified occupations. Each person’s name is unique in the table, and each person has a unique id. The first six rows of this table are shown in Figure 4, to give an idea of the structure of the table.

To start, let’s suppose Megan works as a banker. We could issue a simple UPDATE command to the database to change the occupation field in the table for her, but we’ll write a quick PL/Perl function to illustrate how to send a command to the database. The creation, execution, and verification of this function is shown in Figure 5.

In the set_megan_occupation() function, we create a simple SQL statement and then use spi_exec_query() to run the statement in the database. The result set returned by SELECT set_megan_occupation() contains one row with a NULL value, which happens because set_megan_occupation() RETURNS VOID.

The spi_exec_query() function is part of a group of functions that are used to interact with the database from within PL/Perl. This group of functions is called the Server Programming Interface, or SPI. The DBD::PgSPI Perl module can also be used to access PostgreSQL commands and their results from Perl, but its functionality is experimental at the time of this writing, November, 2007. The most common PL/Perl SPI functions are summarized in Table 1.

Table 1. PL/Perl SPI functions

Suppose each person in the table whose name begins with the letter E is employed as an accountant. Three people in the table have names beginning with the letter E: Erin, Edward, and Elizabeth. Their id values are 10, 33, and 35, respectively. We could write a simple UPDATE command in SQL to reflect their occupations, but again, let’s do it in PL/Perl, instead.

Figure 6 shows a function that finds all the people whose names match a certain regular expression and then sets their occupation. Both the regular expression and the occupation are passed into the function as parameters.