Chapter 21. PL/Perl - Perl Procedural Language

Table of Contents
21.1. PL/Perl Functions and Arguments
21.2. Data Values in PL/Perl
21.3. Database Access from PL/Perl
21.4. Trusted and Untrusted PL/Perl
21.5. Missing Features

PL/Perl is a loadable procedural language that enables you to write PostgreSQL functions in the Perl programming language.

To install PL/Perl in a particular database, use createlang plperl dbname.

Tip: If a language is installed into template1, all subsequently created databases will have the language installed automatically.

Note: Users of source packages must specially enable the build of PL/Perl during the installation process (refer to the installation instructions for more information). Users of binary packages might find PL/Perl in a separate subpackage.

21.1. PL/Perl Functions and Arguments

To create a function in the PL/Perl language, use the standard syntax:

CREATE FUNCTION funcname (argument-types) RETURNS return-type AS '
    # PL/Perl function body
' LANGUAGE plperl;

The body of the function is ordinary Perl code.

Arguments and results are handled as in any other Perl subroutine: Arguments are passed in @_, and a result value is returned with return or as the last expression evaluated in the function. For example, a function returning the greater of two integer values could be defined as:

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS '
    if ($_[0] > $_[1]) { return $_[0]; }
    return $_[1];
' LANGUAGE plperl;

If an SQL null value is passed to a function, the argument value will appear as "undefined" in Perl. The above function definition will not behave very nicely with null inputs (in fact, it will act as though they are zeroes). We could add STRICT to the function definition to make PostgreSQL do something more reasonable: if a null value is passed, the function will not be called at all, but will just return a null result automatically. Alternatively, we could check for undefined inputs in the function body. For example, suppose that we wanted perl_max with one null and one non-null argument to return the non-null argument, rather than a null value:

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS '
    my ($a,$b) = @_;
    if (! defined $a) {
        if (! defined $b) { return undef; }
        return $b;
    }
    if (! defined $b) { return $a; }
    if ($a > $b) { return $a; }
    return $b;
' LANGUAGE plperl;

As shown above, to return an SQL null value from a PL/Perl function, return an undefined value. This can be done whether the function is strict or not.

Composite-type arguments are passed to the function as references to hashes. The keys of the hash are the attribute names of the composite type. Here is an example:

CREATE TABLE employee (
    name text,
    basesalary integer,
    bonus integer
);

CREATE FUNCTION empcomp(employee) RETURNS integer AS '
    my ($emp) = @_;
    return $emp->{''basesalary''} + $emp->{''bonus''};
' LANGUAGE plperl;

SELECT name, empcomp(employee) FROM employee;

There is currently no support for returning a composite-type result value.

Tip: Because the function body is passed as an SQL string literal to CREATE FUNCTION, you have to escape single quotes and backslashes within your Perl source, typically by doubling them as shown in the above example. Another possible approach is to avoid writing single quotes by using Perl's extended quoting operators (q[], qq[], qw[]).