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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/plperl.sgml,v 2.11 2001/11/21 05:53:41 thomas Exp $
-->
<chapter id="plperl">
<title>PL/Perl - Perl Procedural Language</title>
<indexterm zone="plperl">
<primary>PL/Perl</primary>
</indexterm>
<indexterm zone="plperl">
<primary>Perl</primary>
</indexterm>
<para>
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
<productname>PostgreSQL</productname>.
</para>
<para>
The PL/Perl interpreter (when installed as trusted interpreter with
default name <literal>plperl</>) interpreter 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 file handle
operations, <literal>require</literal>, and <literal>use</literal>
(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.
</para>
<para>
When PL/Perl is installed as <quote>untrusted</> interpreter (with name <literal>plperlu</literal>),
everything is permitted, and any Perl code can be loaded (by superuser only).
</para>
<sect1 id="plperl-install">
<title>Building and Installing</title>
<para>
In order to build and install PL/Perl if you are installing
<productname>PostgreSQL</productname> from source then the
<option>--with-perl</option> must be supplied to the
<indexterm><primary><filename>configure</filename></primary></indexterm>
<filename>configure</filename> script. PL/Perl requires that, when
<productname>Perl</productname> was installed, the
<indexterm><primary>libperl</primary></indexterm>
<filename>libperl</filename> 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:
<screen>
<computeroutput>
*****
* Cannot build PL/Perl because libperl is not a shared library.
* Skipped.
*****
</computeroutput>
</screen>
Therefore it is likely that you will have to re-build and install
<productname>Perl</productname> manually to be able to build
PL/Perl.
</para>
<para>
When you want to retry to build PL/Perl after having reinstalled
Perl, then change to the directory
<filename>src/pl/plperl</filename> in the
<productname>PostgreSQL</productname> source tree and issue the commands
<programlisting>
gmake clean
gmake all
gmake install
</programlisting>
</para>
<para>
The <command>createlang</command> command is used to install the
language into a database.
<screen>
<prompt>$</prompt> <userinput>createlang plperl template1</userinput>
</screen>
Alternatively, to create untrusted interpreter (where functions can only
be created by superuser, but the functions are not restricted), use:
<screen>
<prompt>$</prompt> <userinput>createlang plperlu template1</userinput>
</screen>
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 integer,
bonus integer
);
</programlisting>
In order to get the total compensation (base + bonus) we could
define a function as follows:
<programlisting>
CREATE FUNCTION totalcomp(integer, integer) RETURNS integer
AS 'return $_[0] + $_[1]'
LANGUAGE 'plperl';
</programlisting>
Notice that the arguments to the function are passed in
<varname>@_</varname> as might be expected.
</para>
<para>
We can 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 functions:
<programlisting>
CREATE FUNCTION empcomp(employee) RETURNS integer AS '
my $emp = shift;
return $emp->{''basesalary''} + $emp->{''bonus''};
' LANGUAGE 'plperl';
</programlisting>
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.
</para>
<tip>
<para>
Because the function body is passed as an SQL string literal to
<command>CREATE FUNCTION</command> you have to escape single
quotes within your Perl source, either by doubling them as shown
above, or by using the extended quoting functions
(<literal>q[]</literal>, <literal>qq[]</literal>,
<literal>qw[]</literal>). Backslashes must be escaped by doubling
them.
</para>
</tip>
<para>
The new function <function>empcomp</function> can used like:
<programlisting>
SELECT name, empcomp(employee) FROM employee;
</programlisting>
</para>
<para>
Here is an example of a function that will not work because file
system operations are not allowed for security reasons:
<programlisting>
CREATE FUNCTION badfunc() RETURNS integer AS '
open(TEMP, ">/tmp/badfile");
print TEMP "Gotcha!\n";
return 1;
' LANGUAGE 'plperl';
</programlisting>
The creation of the function will succeed, but executing it will not.
Note that if same function was created by superuser using language
<literal>plperlu</>, execution would succeed.
</para>
<para>
Access to database itself from your Perl function can be done via
an experimental module <ulink url="http://www.formenos.org/PgSPI/"><literal>DBD::PgSPI</literal></ulink>. This module makes available a <acronym>DBI</>-compliant
database-handle named <varname>$pg_dbh</varname>, and you can use that to make queries with
normal <acronym>DBI</> syntax.
</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:
-->
|
