How to Use the PL/Perl Language in PostgreSQL

Long before Python became the go-to scripting language for data processing, Perl was the workhorse language for text manipulation, regular expressions, and quick procedural scripts. PostgreSQL still ships with strong support for this heritage through PL/Perl, a procedural language extension that lets me write database functions and triggers using standard Perl syntax. If I need powerful regular expression handling, or I’m maintaining a system built years ago on Perl conventions, PL/Perl is still a genuinely capable tool sitting right inside PostgreSQL.

In this article, I’ll cover what PL/Perl is, how to install and enable it, the syntax for writing functions and triggers, how parameters and return values work, practical examples I’ve used in real projects, common troubleshooting scenarios, and the best practices I follow whenever I reach for it.

What Is PL/Perl?

PL/Perl is a procedural language handler bundled with PostgreSQL’s standard “contrib” style extensions, allowing me to define functions where the body is written in Perl instead of SQL or PL/pgSQL. It comes in two flavors:

• plperl — the trusted variant, sandboxed so that ordinary database users can create functions without being able to access the filesystem or network directly.
• plperlu — the untrusted variant, which removes those restrictions and is limited to database superusers, since it allows arbitrary system-level operations.

Because PL/Perl ships alongside PostgreSQL itself (as long as it was compiled with Perl support, which most standard distribution packages are), it tends to be easier to get running than PL/PHP or PL/Java, which usually require separate installation steps.

Why Choose PL/Perl?

There are a few specific strengths that make PL/Perl worth knowing:

• World-class regular expressions. Perl’s regex engine is one of the most powerful and expressive available in any language, and PL/Perl gives me direct access to it inside SQL functions.
• Compact text processing. For tasks like parsing log lines, cleaning messy text fields, or extracting structured data from unstructured strings, Perl’s syntax is often shorter and more expressive than equivalent PL/pgSQL.
• Mature and stable. PL/Perl has been part of PostgreSQL for a very long time, so its behavior is well understood and well documented.
• Good fit for legacy systems. If I’m maintaining an older PostgreSQL deployment that already leans on PL/Perl functions, understanding the language is essential rather than optional.

Installing and Enabling PL/Perl

On most Linux distributions, PL/Perl is available as a separate package tied to the main PostgreSQL server package:

sudo apt-get install postgresql-plperl-16

Once installed at the OS level, I enable it inside a specific database with:

CREATE EXTENSION plperl;

If I need the untrusted variant for filesystem or network access:

CREATE EXTENSION plperlu;

I can confirm it’s active by checking the list of installed languages:

SELECT lanname FROM pg_language;

Basic Syntax of a PL/Perl Function

A PL/Perl function body is just Perl code wrapped inside a $$ (or custom) dollar-quoted string, with the language specified as plperl:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    my ($a, $b) = @_;
    return $a + $b;
$$ LANGUAGE plperl;

Function arguments are available in Perl’s standard @_ array, exactly like a normal Perl subroutine. I can also access them via the $_[0], $_[1] style, but I generally prefer unpacking into named variables for readability.

SELECT add_numbers(6, 8);

Working with Parameters and Return Types

Scalar Return Values

CREATE OR REPLACE FUNCTION greet_user(name TEXT)
RETURNS TEXT
AS $$
    my ($name) = @_;
    return "Hello, $name! Welcome aboard.";
$$ LANGUAGE plperl;

SELECT greet_user('Marcus');
-- Hello, Marcus! Welcome aboard.

Returning Arrays

PL/Perl automatically maps Perl arrays to PostgreSQL array types:

CREATE OR REPLACE FUNCTION split_csv(input TEXT)
RETURNS TEXT[]
AS $$
    my ($input) = @_;
    my @parts = split(/,/, $input);
    return \@parts;
$$ LANGUAGE plperl;

SELECT split_csv('apple,banana,cherry');
-- {apple,banana,cherry}

Returning Sets

For SETOF return types, I return an array reference of values, and PostgreSQL expands it into rows:

CREATE OR REPLACE FUNCTION list_even_numbers(max_val INTEGER)
RETURNS SETOF INTEGER
AS $$
    my ($max_val) = @_;
    my @results;
    for (my $i = 0; $i <= $max_val; $i += 2) {
        push(@results, $i);
    }
    return \@results;
$$ LANGUAGE plperl;

Returning Composite Types

For structured, multi-column results, I return a hash reference:

CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER)
RETURNS TABLE(id INTEGER, full_name TEXT, is_active BOOLEAN)
AS $$
    my ($user_id) = @_;
    return {
        id => $user_id,
        full_name => "Jane Doe",
        is_active => 1
    };
$$ LANGUAGE plperl;

Regular Expressions: Where PL/Perl Really Shines

This is probably the single biggest reason I reach for PL/Perl over PL/pgSQL. Here’s a function that extracts all email addresses from a block of text:

CREATE OR REPLACE FUNCTION extract_emails(input TEXT)
RETURNS TEXT[]
AS $$
    my ($input) = @_;
    my @emails = ($input =~ /[\w.+-]+\@[\w-]+\.[\w.-]+/g);
    return \@emails;
$$ LANGUAGE plperl;

SELECT extract_emails('Contact us at support@example.com or sales@example.org for help.');
-- {support@example.com,sales@example.org}

Trying to write the equivalent logic in raw PL/pgSQL using regexp_matches() is certainly possible, but Perl’s regex syntax and built-in match operators tend to be more compact and readable for this kind of pattern extraction, especially with more complex patterns.

Writing Trigger Functions in PL/Perl

PL/Perl trigger functions have access to a special %_TD hash containing information about the trigger event, including the new and old row data:

CREATE OR REPLACE FUNCTION normalize_email()
RETURNS trigger
AS $$
    if (defined($_TD->{new}{email})) {
        $_TD->{new}{email} = lc(trim($_TD->{new}{email}));
    }
    return "MODIFY";
$$ LANGUAGE plperl;

CREATE TRIGGER trg_normalize_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION normalize_email();

The special return value "MODIFY" tells PostgreSQL that the trigger function changed the row data and the modified version should be used. Other valid return values include "SKIP" to suppress the operation entirely.

Querying the Database from PL/Perl

PL/Perl provides spi_exec_query() for running SQL directly from inside a function:

CREATE OR REPLACE FUNCTION get_order_total(order_id INTEGER)
RETURNS NUMERIC
AS $$
    my ($order_id) = @_;
    my $result = spi_exec_query(
        "SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = $order_id"
    );
    return $result->{rows}[0]->{total};
$$ LANGUAGE plperl;

Just like with PL/PHP, I strongly prefer parameterized queries using spi_prepare() and spi_exec_prepared() over raw string interpolation whenever the input comes from an untrusted source:

CREATE OR REPLACE FUNCTION get_order_total_safe(order_id INTEGER)
RETURNS NUMERIC
AS $$
    my ($order_id) = @_;
    my $plan = spi_prepare(
        "SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = \$1",
        "INTEGER"
    );
    my $result = spi_exec_prepared($plan, $order_id);
    spi_freeplan($plan);
    return $result->{rows}[0]->{total};
$$ LANGUAGE plperl;

Common Use Cases

Based on real projects I’ve worked on, PL/Perl tends to be most useful for:

1. Text parsing and cleanup — extracting structured data from log entries, addresses, or free-text fields using regex.
2. Data validation triggers — checking formats for emails, phone numbers, or postal codes before insert.
3. String transformation utilities — case conversion, whitespace normalization, and templated text generation.
4. Legacy Perl codebase integration — reusing existing Perl subroutines with minimal modification inside the database layer.

Troubleshooting Common Issues

“language plperl does not exist” error This means the extension hasn’t been created in the current database yet. I run CREATE EXTENSION plperl; as a superuser, and if that fails, I check that the OS-level package (postgresql-plperl-<version>) was actually installed alongside PostgreSQL.

Functions work in psql but fail with permissions issues for application users Trusted PL/Perl functions run with the privileges of the calling user by default for SQL access, but the interpreter sandbox itself restricts filesystem and network calls regardless of user role. If a function genuinely needs those capabilities, I either move that logic to plperlu and restrict who can create such functions, or push the responsibility to the application layer instead.

Unexpected results with array parameters PL/Perl’s mapping between PostgreSQL arrays and Perl array references can trip people up, especially with multi-dimensional arrays. I always test edge cases like empty arrays and NULL elements explicitly, since Perl’s handling of undefined values doesn’t always match PostgreSQL’s NULL semantics.

Regex behaving differently than expected Perl regex has its own dialect and quirks (particularly around greedy vs. non-greedy matching and Unicode handling). When a pattern isn’t matching as expected, I test it in isolation using perl -e outside the database first, which isolates whether the issue is the regex itself or something about how PL/Perl is passing the input.

Performance degradation under high concurrency Each PL/Perl function call runs inside an embedded Perl interpreter tied to the backend process. For extremely hot code paths called millions of times, I benchmark against an equivalent PL/pgSQL or SQL-only implementation, since Perl interpreter overhead can add up for very tight loops.

Best Practices

• Reserve PL/Perl for text-heavy logic. For pure numeric or set-based operations, PL/pgSQL or plain SQL will typically outperform it.
• Always use spi_prepare()/spi_exec_prepared() for dynamic queries. Never interpolate raw untrusted input directly into a query string.
• Test regex patterns independently before embedding them. This saves a lot of debugging time compared to iterating directly inside CREATE FUNCTION statements.
• Be explicit about NULL handling. Perl’s truthiness rules don’t map one-to-one with SQL NULL, so I check defined() explicitly rather than relying on Perl’s implicit boolean conversion.
• Use plperl, not plperlu, unless there’s a clear need. Sticking with the trusted variant keeps the sandboxing intact and limits what a compromised or buggy function can do.
• Keep functions short and single-purpose. Long Perl blocks embedded in SQL statements become difficult to read, test, and maintain over time — I extract complex logic into well-named helper functions where possible.
• Document non-obvious regex patterns. A complex regular expression buried in a trigger function is a maintenance trap without a comment explaining what it’s matching and why.

Comparing PL/Perl to Other Procedural Languages

When I weigh PL/Perl against PL/pgSQL, the deciding factor is almost always text complexity — for simple string operations, PL/pgSQL’s built-in functions are perfectly adequate, but once I need lookahead assertions, named capture groups, or multi-pattern alternation, Perl’s regex engine is simply more expressive and easier to write correctly.

Against PL/Python, the two overlap more than people expect, since both are general-purpose scripting languages with strong text-handling capabilities. Python’s re module is capable but generally considered less expressive than Perl’s native regex syntax, which is, after all, where a huge portion of modern regex conventions originated. On the other hand, Python wins clearly on general ecosystem breadth, community size, and readability for anyone not already familiar with Perl’s more terse syntax conventions.

Against PL/PHP, PL/Perl has the advantage of being bundled directly with standard PostgreSQL packaging in most distributions, so there’s no separate extension build process required, unlike PL/PHP which often needs to be compiled from source. This alone makes PL/Perl meaningfully easier to get running in a fresh environment.

Security Considerations

PL/Perl’s trusted variant (plperl) sandboxes the interpreter using a restricted Perl “Opcode” mask, preventing functions from accessing the filesystem, opening network sockets, or executing arbitrary system commands. This is a genuinely useful security property compared to PL/Python, which offers no trusted variant at all in current PostgreSQL versions.

A few things I always keep in mind:

Trusted mode limitations are a feature, not a bug. If a plperl function needs filesystem or network access and I find myself tempted to switch to plperlu just to make an error go away, I stop and reconsider whether that logic actually belongs inside the database at all, rather than immediately reaching for the untrusted variant.

Granting plperlu usage is a serious privilege. Just like the other untrusted languages, I restrict plperlu function creation to superusers or a very small, deliberately chosen set of trusted roles.

Dynamic SQL discipline still applies. Even though plperl is sandboxed against filesystem and network access, it still has full access to the database itself through spi_exec_query() and spi_prepare(). SQL injection risk is unrelated to the trusted/untrusted distinction, and I apply the same parameterized-query discipline regardless of which variant I’m using.

Performance Tuning Tips

• Precompile regex patterns used repeatedly. Perl caches compiled regex patterns internally in many cases, but for functions called extremely frequently, I structure code to avoid recompiling complex patterns unnecessarily within a loop.
• Avoid PL/Perl for simple numeric operations. For anything that’s fundamentally arithmetic or set-based rather than text-processing-heavy, PL/pgSQL or plain SQL will consistently outperform an equivalent Perl implementation.
• Batch SPI calls where possible. Calling spi_exec_query() repeatedly inside a tight loop (once per row, for example) adds overhead compared to a single well-constructed query that returns everything needed at once.
• Watch memory usage in functions handling large text blobs. Perl’s internal string handling can use more memory than expected when processing very large text fields repeatedly; I test with realistic data sizes rather than only small sample strings during development.

Frequently Asked Questions

Does PL/Perl support Perl modules from CPAN? Trusted plperl functions can only use a restricted subset of “safe” modules explicitly allowed by the sandbox. Untrusted plperlu functions have much broader access to installed CPAN modules, but this requires superuser-level trust, and I make sure any required modules are actually installed in the Perl environment PostgreSQL was built against.

Can I write PL/Perl trigger functions that skip the operation entirely? Yes — returning the string "SKIP" from a BEFORE trigger function tells PostgreSQL to silently skip the triggering operation for that row, which is useful for conditional soft-delete or filtering logic implemented at the trigger level.

How do I debug a PL/Perl function that’s failing silently? I add elog(NOTICE, ...)-style output using PL/Perl’s elog() function at key points in the logic, which surfaces messages back to the client exactly like RAISE NOTICE does in PL/pgSQL, making it much easier to trace execution step by step.

Inspecting and Managing PL/Perl Functions

Like any other PostgreSQL function, PL/Perl functions show up in the standard catalog views, so I can inspect them without needing any Perl-specific tooling:

\df+ extract_emails

SELECT proname, prosrc, provolatile
FROM pg_proc
WHERE proname = 'extract_emails';

Checking prosrc here shows me the raw Perl source stored for the function, which is genuinely useful when auditing an unfamiliar database and trying to understand what a mysteriously named trigger function actually does before touching it.

Dropping a PL/Perl function follows the same rules as any other function — I include the parameter types explicitly to avoid ambiguity if overloaded versions exist:

DROP FUNCTION IF EXISTS extract_emails(TEXT);

Final Thoughts

PL/Perl earns its place in the PostgreSQL ecosystem primarily through its unmatched regular expression engine and its long track record of stability as a bundled procedural language. I reach for it specifically when I need serious text processing power close to the data, or when I’m maintaining a system that already has Perl-based logic baked into its triggers and functions. For general-purpose procedural logic, I’d still lean on PL/pgSQL first, but PL/Perl remains one of the more elegant tools in the box whenever the problem is fundamentally about parsing or transforming text.