Using Perl in PostgreSQL

by Andrew Dunstan
11/10/2005

Most Perl users are familiar with using Perl to talk to databases. Perl's DBI is, along with ODBC and JDBC, one of the most common and widely ported database client interfaces. The DBI driver for PostgreSQL, DBD::Pg, is very well-maintained, and quite featureful. For example, it recently acquired proper support for prepared statements. Previously, the client library had emulated these, but with the latest DBD::Pg and PostgreSQL distributions, you can get real prepared queries, which can lead to big performance gains in some cases.

However, there is another way of using Perl with PostgreSQL--writing little Perl programs that actually execute inside of the server. This way of using Perl is less well known than using the DBI driver, and is, as far as I know, unique to PostgreSQL. It lets you do some very cool things that you just can't do in the client.

Server-Side Languages

In fact, PostgreSQL lets you create server-side routines in quite a few languages, including one called PL/PGSQL that is all its own, and is somewhat similar to Oracle's PL/SQL. The PostgreSQL core distribution supports and maintains three other procedural language interfaces to third-party interpreters: Perl, Python, and Tcl (the first procedural language that PostgreSQL supported). There are also other languages maintained outside of the core distribution for various reasons, including PL/Java (or an alternative flavor, PL/J), Pl/R, PL/Ruby, PL/PHP, and a vastly better PL/Python. If you, like me, are at home in Perl, you will probably want to write your server-side functions in Perl, too.

PostgreSQL languages come in two flavors: trusted and untrusted. Trusted languages are those that guarantee not to touch the file system, or other machine resources, while untrusted languages make no such promise. Postgres also protects your machine by refusing to run as root (or a similarly privileged user on Windows). But on a database server, your most valuable asset is probably the data itself, and so you need some additional protection where code might maliciously attack your data via access to the machine's resources. For that reason, only highly privileged database users ("superusers") can create functions in untrusted languages. Only they are allowed to install procedural languages at all, trusted or untrusted. Unless you have installed another language, the only ones available are SQL (which is trusted) and C (which is untrusted).

Enabling PL/Perl

PL/Perl actually comes in both of these flavors--the trusted version runs inside the standard Perl Safe container, with very few native Perl operations allowed. The easiest way to install either flavor of PL/Perl in a database is via the createlang program that should be part of your distribution. For example:

$ createlang plperl mydb

For the untrusted version, use instead:

$ createlang plperlu mydb

A Simple Example

The easy way to show how to use PL/Perl is to create a very simple function; one that would be a lot harder to do otherwise. Suppose that you want to test if a given piece of text is a palindrome (a word that reads the same backwards as forwards), disregarding white space and the case of the letters. Here's a piece of SQL to define the function:

create function palindrome(text) returns boolean 
language plperl 
immutable
as '

  my $arg = shift;
  my ($canonical = lc $arg) =~ s/\s+//g;
  return ($canonical eq reverse $canonical) ? "true" : "false";

';

Given this function, you can write SQL like:

select name, region, country, population
from towns
where palindrome(name);

If you can't build a test like this on the server side, you have to get all of the towns in the client and filter there, but that's horribly inefficient. Getting the server to test for you is far nicer.

The create function statement declares a function. It requires a name, an argument type list (which can be empty, but you must use the parentheses), a result type, and a language. In this case, I added a further argument, immutable, which tells Postgres that I guarantee that the function value depends only on its input, enabling it to do some optimization. Finally, there is the AS clause, followed by an SQL string literal that contains the body of the function.

The body is actually the body of an anonymous subroutine--the glue code wraps it up in a call to the Perl interpreter to create this subroutine and return the reference to the glue code, which stashes it away for later use. The glue code also stores the text in the database catalogs for later retrieval if necessary. Postgres compiles each function once per database session; it does not cache any Perl bytecode. From a programmer's point of view, remember that it wraps up your code in something like:

sub { <your text> }

PLPerl function arguments appear in @_, just like in regular Perl subroutines, and your code can handle them the same way. The string that contains the body is a normal SQL string and has to obey the same escaping rules as other SQL strings. Because this can lead to some considerable ugliness with strings that need reparsing, version 8.0 of PostgreSQL introduced an alternative quoting mechanism. Implemented by Tom Lane and me, it's colloquially known as "dollar quoting," and can make function bodies more readable in SQL code. I'll use that for my subsequent examples.

The argument arrives as a string, no matter what type it has in the database. For simple types, just return a string, which must be a valid literal of the return type. The SQL value NULL maps to the Perl value undef, both for arguments and for return values.