Perl is a feature-rich language, which has been around for a long time. PostgreSQL allows you to write Perl routines that are stored and executed inside the database. This ability is quite unique to PostgreSQL and allows you to do a lot of cool things, such as using Perl's text manipulation features inside a database. PL/Perl is one of the many languages that PostgreSQL supports for writing server-side routines.
As discussed in the earlier chapters, PostgreSQL supports trusted and untrusted languages. PL/Perl is available in both these flavors. The trusted version runs inside a safe container and, therefore, not the entire set of familiar native Perl operations is allowed.
In this chapter, we will cover the following topics:
- When to use PL/Perl
- How to install and write a basic function
- Passing arguments to and from PL/Perl functions
- Writing triggers in PL/Perl
- A brief introduction to untrusted PL/Perl
We will briefly discuss how to create Perl functions and use them in triggers. Some of you might ask when it is beneficial to use PL/Perl, since PostgreSQL supports a variety of languages that can be used to create triggers or write stored procedures/functions.
You might be more familiar with a language such as Perl than with Python or Tcl. This is a pretty good reason to choose a certain language over the other.
However, if the performance of the function is one of your considerations, then you might want to choose a language based on the nature of the code you want to write.
In general, PL/Perl will outperform PL/pgSQL, if the focus of the stored procedure is computational tasks, athematic, and string parsing and manipulation. However, PL/pgSQL will often be a clear winner if you need to access the database. PL/pgSQL is closely tied into the PostgreSQL execution engine and has a very low overhead of running a query, compared to other procedural languages.