PL/Perl allows you to write functions in the Perl programming language that may be used in SQL queries as if they were built into Postgres. The PL/Perl intepreter is a full Perl interpreter. However, certain operations have been disabled in order to maintain the security of the system. In general, the operations that are restricted are those that interact with the environment. This includes filehandle operations, require, and use (for external modules). It should be noted that this security is not absolute. Indeed, several Denial-of-Service attacks are still possible - memory exhaustion and endless loops are two examples. In order to build and install PL/Perl if you are installing Postgres from source then the --with-perl must be supplied to the configure script. PL/Perl requires that, when Perl was installed, the libperl library was build as a shared object. At the time of this writing, this is almost never the case in the Perl packages that are distributed with the operating systems. A message like this will appear during the build to point out this fact: ***** * Cannot build PL/Perl because libperl is not a shared library. * Skipped. ***** Therefore it is likely that you will have to re-build and install Perl manually to be able to build PL/Perl. When you want to retry to build PL/Perl after having reinstalled Perl, then change to the directory src/pl/plperl in the Postgres source tree and issue the commands gmake clean gmake all gmake install The createlang command is used to install the language into a database. $ createlang plperl template1 If it is installed into template1, all future databases will have the language installed automatically. Assume you have the following table: CREATE TABLE EMPLOYEE ( name text, basesalary integer, bonus integer ); In order to get the total compensation (base + bonus) we could define a function as follows: CREATE FUNCTION totalcomp(integer, integer) RETURNS integer AS 'return $_[0] + $_[1]' LANGUAGE 'plperl'; Notice that the arguments to the function are passed in @_ as might be expected. We can now use our function like so: SELECT name, totalcomp(basesalary, bonus) FROM employee; But, we can also pass entire tuples to our functions: CREATE FUNCTION empcomp(employee) RETURNS integer AS ' my $emp = shift; return $emp->{''basesalary''} + $emp->{''bonus''}; ' LANGUAGE 'plperl'; A tuple is passed as a reference to a hash. The keys are the names of the fields in the tuples. The hash values are values of the corresponding fields in the tuple. Because the function body is passed as an SQL string literal to CREATE FUNCTION you have to escape single quotes within your Perl source, either by doubling them as shown above, or by using the extended quoting functions (q[], qq[], qw[]). Backslashes must be escaped by doubling them. The new function empcomp can used like: SELECT name, empcomp(employee) FROM employee; Here is an example of a function that will not work because file system operations are not allowed for security reasons: CREATE FUNCTION badfunc() RETURNS integer AS ' open(TEMP, ">/tmp/badfile"); print TEMP "Gotcha!\n"; return 1; ' LANGUAGE 'plperl'; The creation of the function will succeed, but executing it will not.