2
$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.37 2005-01-17 17:29:49 momjian Exp $
6
<title>PL/Perl - Perl Procedural Language</title>
8
<indexterm zone="plperl">
9
<primary>PL/Perl</primary>
12
<indexterm zone="plperl">
13
<primary>Perl</primary>
17
PL/Perl is a loadable procedural language that enables you to write
18
<productname>PostgreSQL</productname> functions in the <ulink
19
url="http://www.perl.com">Perl</ulink> programming language.
23
To install PL/Perl in a particular database, use
24
<literal>createlang plperl <replaceable>dbname</></literal>.
29
If a language is installed into <literal>template1</>, all subsequently
30
created databases will have the language installed automatically.
36
Users of source packages must specially enable the build of
37
PL/Perl during the installation process. (Refer to <xref
38
linkend="install-short"> for more information.) Users of
39
binary packages might find PL/Perl in a separate subpackage.
43
<sect1 id="plperl-funcs">
44
<title>PL/Perl Functions and Arguments</title>
47
To create a function in the PL/Perl language, use the standard syntax:
49
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
50
# PL/Perl function body
53
The body of the function is ordinary Perl code.
57
The syntax of the <command>CREATE FUNCTION</command> command requires
58
the function body to be written as a string constant. It is usually
59
most convenient to use dollar quoting (see <xref
60
linkend="sql-syntax-dollar-quoting">) for the string constant.
61
If you choose to use regular single-quoted string constant syntax,
62
you must escape single quote marks (<literal>'</>) and backslashes
63
(<literal>\</>) used in the body of the function, typically by
64
doubling them (see <xref linkend="sql-syntax-strings">).
68
Arguments and results are handled as in any other Perl subroutine:
69
arguments are passed in <varname>@_</varname>, and a result value
70
is returned with <literal>return</> or as the last expression
71
evaluated in the function.
75
For example, a function returning the greater of two integer values
79
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
80
if ($_[0] > $_[1]) { return $_[0]; }
87
If an SQL null value<indexterm><primary>null value</><secondary
88
sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
89
the argument value will appear as <quote>undefined</> in Perl. The
90
above function definition will not behave very nicely with null
91
inputs (in fact, it will act as though they are zeroes). We could
92
add <literal>STRICT</> to the function definition to make
93
<productname>PostgreSQL</productname> do something more reasonable:
94
if a null value is passed, the function will not be called at all,
95
but will just return a null result automatically. Alternatively,
96
we could check for undefined inputs in the function body. For
97
example, suppose that we wanted <function>perl_max</function> with
98
one null and one nonnull argument to return the nonnull argument,
99
rather than a null value:
102
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
105
if (! defined $b) { return undef; }
108
if (! defined $b) { return $a; }
109
if ($a > $b) { return $a; }
113
As shown above, to return an SQL null value from a PL/Perl
114
function, return an undefined value. This can be done whether the
115
function is strict or not.
119
Composite-type arguments are passed to the function as references
120
to hashes. The keys of the hash are the attribute names of the
121
composite type. Here is an example:
124
CREATE TABLE employee (
130
CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
132
return $emp->{basesalary} + $emp->{bonus};
135
SELECT name, empcomp(employee.*) FROM employee;
140
A PL/Perl function can return a composite-type result using the same
141
approach: return a reference to a hash that has the required attributes.
145
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
147
CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
148
return {f2 => 'hello', f1 => 1, f3 => 'world'};
151
SELECT * FROM perl_row();
154
Any columns in the declared result data type that are not present in the
155
hash will be returned as NULLs.
159
PL/Perl functions can also return sets of either scalar or composite
160
types. To do this, return a reference to an array that contains
161
either scalars or references to hashes, respectively. Here are
162
some simple examples:
165
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
169
SELECT * FROM perl_set_int(5);
172
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
174
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
175
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
176
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
180
SELECT * FROM perl_set();
183
Note that when you do this, Perl will have to build the entire array in
184
memory; therefore the technique does not scale to very large result sets.
188
<application>PL/Perl</> does not currently have full support for
189
domain types: it treats a domain the same as the underlying scalar
190
type. This means that constraints associated with the domain will
191
not be enforced. This is not an issue for function arguments, but
192
it is a hazard if you declare a <application>PL/Perl</> function
193
as returning a domain type.
197
<sect1 id="plperl-database">
198
<title>Database Access from PL/Perl</title>
201
Access to the database itself from your Perl function can be done
202
via the function <function>spi_exec_query</function> described
203
below, or via an experimental module <ulink
204
url="http://www.cpan.org/modules/by-module/DBD/APILOS/"><literal>DBD::PgSPI</literal></ulink>
205
(also available at <ulink
206
url="http://www.cpan.org/SITES.html"><acronym>CPAN</> mirror
207
sites</ulink>). This module makes available a
208
<acronym>DBI</>-compliant database-handle named
209
<varname>$pg_dbh</varname> that can be used to perform queries with
210
normal <acronym>DBI</>
211
syntax.<indexterm><primary>DBI</></indexterm>
215
PL/Perl itself presently provides two additional Perl commands:
220
<primary>spi_exec_query</primary>
221
<secondary>in PL/Perl</secondary>
224
<term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
225
<term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
228
Executes an SQL command. Here is an example of a query
229
(<command>SELECT</command> command) with the optional maximum
232
$rv = spi_exec_query('SELECT * FROM my_table', 5);
234
This returns up to 5 rows from the table
235
<literal>my_table</literal>. If <literal>my_table</literal>
236
has a column <literal>my_column</literal>, you can get that
237
value from row <literal>$i</literal> of the result like this:
239
$foo = $rv->{rows}[$i]->{my_column};
241
The total number of rows returned from a <command>SELECT</command>
242
query can be accessed like this:
244
$nrows = $rv->{processed}
249
Here is an example using a different command type:
251
$query = "INSERT INTO my_table VALUES (1, 'test')";
252
$rv = spi_exec_query($query);
254
You can then access the command status (e.g.,
255
<literal>SPI_OK_INSERT</literal>) like this:
257
$res = $rv->{status};
259
To get the number of rows affected, do:
261
$nrows = $rv->{processed};
266
Here is a complete example:
273
INSERT INTO test (i, v) VALUES (1, 'first line');
274
INSERT INTO test (i, v) VALUES (2, 'second line');
275
INSERT INTO test (i, v) VALUES (3, 'third line');
276
INSERT INTO test (i, v) VALUES (4, 'immortal');
278
CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
280
my $rv = spi_exec_query('select i, v from test;');
281
my $status = $rv->{status};
282
my $nrows = $rv->{processed};
283
foreach my $rn (0 .. $nrows - 1) {
284
my $row = $rv->{rows}[$rn];
285
$row->{i} += 200 if defined($row->{i});
286
$row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
292
SELECT * FROM test_munge();
300
<primary>elog</primary>
301
<secondary>in PL/Perl</secondary>
304
<term><literal><function>elog</>(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</literal></term>
307
Emit a log or error message. Possible levels are
308
<literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
309
<literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
311
raises an error condition; if this is not trapped by the surrounding
312
Perl code, the error propagates out to the calling query, causing
313
the current transaction or subtransaction to be aborted. This
314
is effectively the same as the Perl <literal>die</> command.
315
The other levels only generate messages of different
317
Whether messages of a particular priority are reported to the client,
318
written to the server log, or both is controlled by the
319
<xref linkend="guc-log-min-messages"> and
320
<xref linkend="guc-client-min-messages"> configuration
321
variables. See <xref linkend="runtime-config"> for more
330
<sect1 id="plperl-data">
331
<title>Data Values in PL/Perl</title>
334
The argument values supplied to a PL/Perl function's code are
335
simply the input arguments converted to text form (just as if they
336
had been displayed by a <command>SELECT</command> statement).
337
Conversely, the <literal>return</> command will accept any string
338
that is acceptable input format for the function's declared return
339
type. So, within the PL/Perl function,
340
all values are just text strings.
344
<sect1 id="plperl-global">
345
<title>Global Values in PL/Perl</title>
348
You can use the global hash <varname>%_SHARED</varname> to store
349
data, including code references, between function calls for the
350
lifetime of the current session.
354
Here is a simple example for shared data:
356
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
357
if ($_SHARED{$_[0]} = $_[1]) {
360
return "can't set shared variable $_[0] to $_[1]";
364
CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
365
return $_SHARED{$_[0]};
368
SELECT set_var('sample', 'Hello, PL/Perl! How's tricks?');
369
SELECT get_var('sample');
374
Here is a slightly more complicated example using a code reference:
377
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
378
$_SHARED{myquote} = sub {
380
$arg =~ s/(['\\])/\\$1/g;
385
SELECT myfuncs(); /* initializes the function */
387
/* Set up a function that uses the quote function */
389
CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
390
my $text_to_quote = shift;
391
my $qfunc = $_SHARED{myquote};
392
return &$qfunc($text_to_quote);
396
(You could have replaced the above with the one-liner
397
<literal>return $_SHARED{myquote}->($_[0]);</literal>
398
at the expense of readability.)
402
<sect1 id="plperl-trusted">
403
<title>Trusted and Untrusted PL/Perl</title>
405
<indexterm zone="plperl-trusted">
406
<primary>trusted</primary>
407
<secondary>PL/Perl</secondary>
411
Normally, PL/Perl is installed as a <quote>trusted</> programming
412
language named <literal>plperl</>. In this setup, certain Perl
413
operations are disabled to preserve security. In general, the
414
operations that are restricted are those that interact with the
415
environment. This includes file handle operations,
416
<literal>require</literal>, and <literal>use</literal> (for
417
external modules). There is no way to access internals of the
418
database server process or to gain OS-level access with the
419
permissions of the server process,
420
as a C function can do. Thus, any unprivileged database user may
421
be permitted to use this language.
425
Here is an example of a function that will not work because file
426
system operations are not allowed for security reasons:
428
CREATE FUNCTION badfunc() RETURNS integer AS $$
429
open(TEMP, ">/tmp/badfile");
430
print TEMP "Gotcha!\n";
434
The creation of the function will succeed, but executing it will not.
438
Sometimes it is desirable to write Perl functions that are not
439
restricted. For example, one might want a Perl function that sends
440
mail. To handle these cases, PL/Perl can also be installed as an
441
<quote>untrusted</> language (usually called
442
<application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
443
In this case the full Perl language is available. If the
444
<command>createlang</command> program is used to install the
445
language, the language name <literal>plperlu</literal> will select
446
the untrusted PL/Perl variant.
450
The writer of a <application>PL/PerlU</> function must take care that the function
451
cannot be used to do anything unwanted, since it will be able to do
452
anything that could be done by a user logged in as the database
453
administrator. Note that the database system allows only database
454
superusers to create functions in untrusted languages.
458
If the above function was created by a superuser using the language
459
<literal>plperlu</>, execution would succeed.
463
<sect1 id="plperl-triggers">
464
<title>PL/Perl Triggers</title>
467
PL/Perl can be used to write trigger functions. In a trigger function,
468
the hash reference <varname>$_TD</varname> contains information about the
469
current trigger event. The fields of the <varname>$_TD</varname> hash
474
<term><literal>$_TD->{new}{foo}</literal></term>
477
<literal>NEW</literal> value of column <literal>foo</literal>
483
<term><literal>$_TD->{old}{foo}</literal></term>
486
<literal>OLD</literal> value of column <literal>foo</literal>
492
<term><literal>$_TD->{name}</literal></term>
495
Name of the trigger being called
501
<term><literal>$_TD->{event}</literal></term>
504
Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
510
<term><literal>$_TD->{when}</literal></term>
513
When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
519
<term><literal>$_TD->{level}</literal></term>
522
The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
528
<term><literal>$_TD->{relid}</literal></term>
531
OID of the table on which the trigger fired
537
<term><literal>$_TD->{relname}</literal></term>
540
Name of the table on which the trigger fired
546
<term><literal>$_TD->{argc}</literal></term>
549
Number of arguments of the trigger function
555
<term><literal>@{$_TD->{args}}</literal></term>
558
Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.
567
Triggers can return one of the following:
571
<term><literal>return;</literal></term>
574
Execute the statement
580
<term><literal>"SKIP"</literal></term>
583
Don't execute the statement
589
<term><literal>"MODIFY"</literal></term>
592
Indicates that the <literal>NEW</literal> row was modified by
601
Here is an example of a trigger function, illustrating some of the
609
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
610
if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
611
return "SKIP"; # skip INSERT/UPDATE command
612
} elsif ($_TD->{new}{v} ne "immortal") {
613
$_TD->{new}{v} .= "(modified by trigger)";
614
return "MODIFY"; # modify row and execute INSERT/UPDATE command
616
return; # execute INSERT/UPDATE command
620
CREATE TRIGGER test_valid_id_trig
621
BEFORE INSERT OR UPDATE ON test
622
FOR EACH ROW EXECUTE PROCEDURE valid_id();
627
<sect1 id="plperl-missing">
628
<title>Limitations and Missing Features</title>
631
The following features are currently missing from PL/Perl, but they
632
would make welcome contributions.
637
PL/Perl functions cannot call each other directly (because they
638
are anonymous subroutines inside Perl).
644
SPI is not yet fully implemented.
650
In the current implementation, if you are fetching or returning
651
very large data sets, you should be aware that these will all go
661
<!-- Keep this comment at the end of the file
666
sgml-minimize-attributes:nil
667
sgml-always-quote-attributes:t
670
sgml-parent-document:nil
671
sgml-default-dtd-file:"./reference.ced"
672
sgml-exposed-tags:nil
673
sgml-local-catalogs:("/usr/lib/sgml/catalog")
674
sgml-local-ecat-files:nil
added
removed
doc/src/sgml/plperl.sgml
