Compose PostgreSQL Gets PL/Perl

TL;DR: We've just added PL/Perl support to all our Compose PostgreSQL deployments.

PostgreSQL has the capability to be extended using various languages. This means that you can use a particular language's syntax and facilities to create procedures stored and executed on the server. The most obvious one is PL/pgSQL which is a procedural variant of PostgreSQL's own SQL, adding variables, loops and control statements to the mix. It also lets you work with the database from the function using SELECT, INSERT, UPDATE and DELETE within pgSQL's vocabulary or create dynamic commands which can be executed on the fly.

It's built into PostgreSQL by default and available to databases automatically. All the PL extensions allow functions to be written, passed arguments from PostgreSQL's SQL and optionally return values to the calling SQL. Functions can also be used as trigger procedures reacting to changes in data within the database.

But SQL isn't the most concise language and neither is PL/pgSQL. Add to that its fairly limited string handling and you might find yourself wanting a more traditional programming language…

That's where Perl comes in because if there's one thing Perl does well, its handle strings with a plethora of string manipulation functions. The database can be accessed through some
builtin Perl functions which take a query string as an argument. For example spiexecquery runs queries returning the result as a array of hash references, good for small sets of results, while spiquery returns a cursor and spifetchrow iterates through the matching results a row at a time. There's also API calls to pre-prepare commands and queries and execute them when needed.

As well as the API, there's a set of built in helpers to manage the transition of variables and values between Perl and the database itself and support for shared global values between Perl functions. Like Pl/pgSQL, Perl functions can also be used with triggers and deliver $_TD, a hash of trigger information, to any Perl trigger functions.

It's installed on Compose in its "Trusted" mode which means that file access, library loading, internals access and OS access are all blocked. This allows us to confidently offer it to our users whilst maintaining the integrity of their PostgreSQL deployments. There is an untrusted variant of Perl which we have no intention of making available for exactly the same reason.

We've added PL/Perl to the Compose PostgreSQL platform because there are some sophisticated tools developed with it, which we'd like to give Compose users access to, but more on that in the coming weeks.