<chapter id="pl-perl">

<title>Perl Procedural Language</title>

<para>

This chapter describes how to compile, install and

use PL/Perl.

</para>

<sect1>

<title>Overview</title>

<para>

PL/Perl allows you to write functions in the Perl scripting

language which may be used in SQL queries as if they were

built into <productname>Postgres</productname>.

</para>

<para>

The PL/Perl intepreter is a full Perl interpreter. However,

certain operations have been disabled in order to increase

the security level of the system.

</para>

<para>

In general, the operations that are restricted are those that

interact with the environment. This includes filehandle operations,

<literal>require</literal>, and <literal>use</literal> (for external

modules).

</para>

<para>

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.

</para>

</sect1>

<sect1>

<title>Building and Installing</title>

<para>

Assuming that the <productname>Postgres</productname>

source tree is rooted at $PGSRC, then the Pl/perl source

code is located in $PGSRC/src/pl/plperl.

</para>

<para>

To build, simply do the following:

<programlisting>

cd $PGSRC/src/pl/plperl

perl Makefile.PL

make

</programlisting>

</para>

<para>

This will create a shared library file. On a Linux system, it will be

named plperl.so. The extension may differ on other systems.

</para>

<para>

The shared library should then be copied into the lib subdirectory of the

postgres installation.

</para>

<para>

The createlang command is used to install the language into a database.

If it is installed into template1, all future databases will have the

language installed automatically.

</para>

</sect1>

<sect1>

<title>Using PL/Perl</title>

<para>

Assume you have the following table:

<programlisting>

CREATE TABLE EMPLOYEE (

name text,

basesalary int4,

bonus int4 );

</programlisting>

In order to get the total compensation (base + bonus) we could

define a function as follows:

<programlisting>

CREATE FUNCTION totalcomp(int4, int4) RETURNS int4

AS 'return $_[0] + $_[1]'

LANGUAGE 'plperl';

</programlisting>

Note that the arguments are passed to the function in

<literal>@_</literal> as might be expected. Also, because

of the quoting rules for the SQL creating the function, you

may find yourself using the extended quoting functions (qq[],

q[], qw[]) more often that you are used to.

</para>

<para>

We may now use our function like so:

<programlisting>

SELECT name, totalcomp(basesalary, bonus) from employee

</programlisting>

</para>

<para>

But, we can also pass entire tuples to our function:

<programlisting>

CREATE FUNCTION empcomp(employee) returns int4

AS 'my $emp = shift;

return $emp->{'basesalary'} + $emp->{'bonus'};'

LANGUAGE 'plperl';

</programlisting>

A tuple is passed as a reference to hash. The keys are the names of

fields in the tuples. The values are values of the corresponding

field in the tuple.

</para>

<para>

The new function <literal>empcomp</literal> can used like:

<programlisting>

SELECT name, empcomp(employee) from employee;

</programlisting>

</para>

</sect1>

</chapter>

<!-- Keep this comment at the end of the file

Local variables:

mode:sgml

sgml-omittag:nil

sgml-shorttag:t

sgml-minimize-attributes:nil

sgml-always-quote-attributes:t

sgml-indent-step:1

sgml-indent-data:t

sgml-parent-document:nil

sgml-default-dtd-file:"./reference.ced"

sgml-exposed-tags:nil

sgml-local-catalogs:("/usr/lib/sgml/CATALOG")

sgml-local-ecat-files:nil

End:

-->