1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/plperl.sgml,v 2.5 2000/09/29 20:21:34 petere Exp $
-->
<chapter id="pl-perl">
<title>PL/perl - Perl Procedural Language</title>
<para>
This chapter describes how to compile, install and
use PL/Perl.
</para>
<sect1 id="plperl-overview">
<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 id="plperl-install">
<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 id="plperl-use">
<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:
-->
|
