1
<!-- doc/src/sgml/plperl.sgml -->
4
<title>PL/Perl - Perl Procedural Language</title>
6
<indexterm zone="plperl">
7
<primary>PL/Perl</primary>
10
<indexterm zone="plperl">
11
<primary>Perl</primary>
15
PL/Perl is a loadable procedural language that enables you to write
16
<productname>PostgreSQL</productname> functions in the
17
<ulink url="http://www.perl.org">Perl programming language</ulink>.
21
The main advantage to using PL/Perl is that this allows use,
22
within stored functions, of the manyfold <quote>string
23
munging</quote> operators and functions available for Perl. Parsing
24
complex strings might be easier using Perl than it is with the
25
string functions and control structures provided in PL/pgSQL.
29
To install PL/Perl in a particular database, use
30
<literal>CREATE EXTENSION plperl</>, or from the shell command line use
31
<literal>createlang plperl <replaceable>dbname</></literal>.
36
If a language is installed into <literal>template1</>, all subsequently
37
created databases will have the language installed automatically.
43
Users of source packages must specially enable the build of
44
PL/Perl during the installation process. (Refer to <xref
45
linkend="installation"> for more information.) Users of
46
binary packages might find PL/Perl in a separate subpackage.
50
<sect1 id="plperl-funcs">
51
<title>PL/Perl Functions and Arguments</title>
54
To create a function in the PL/Perl language, use the standard
55
<xref linkend="sql-createfunction">
59
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
60
# PL/Perl function body
64
The body of the function is ordinary Perl code. In fact, the PL/Perl
65
glue code wraps it inside a Perl subroutine. A PL/Perl function is
66
called in a scalar context, so it can't return a list. You can return
67
non-scalar values (arrays, records, and sets) by returning a reference,
72
PL/Perl also supports anonymous code blocks called with the
73
<xref linkend="sql-do"> statement:
81
An anonymous code block receives no arguments, and whatever value it
82
might return is discarded. Otherwise it behaves just like a function.
87
The use of named nested subroutines is dangerous in Perl, especially if
88
they refer to lexical variables in the enclosing scope. Because a PL/Perl
89
function is wrapped in a subroutine, any named subroutine you place inside
90
one will be nested. In general, it is far safer to create anonymous
91
subroutines which you call via a coderef. For more information, see the
92
entries for <literal>Variable "%s" will not stay shared</literal> and
93
<literal>Variable "%s" is not available</literal> in the
94
<citerefentry><refentrytitle>perldiag</></citerefentry> man page, or
95
search the Internet for <quote>perl nested named subroutine</>.
100
The syntax of the <command>CREATE FUNCTION</command> command requires
101
the function body to be written as a string constant. It is usually
102
most convenient to use dollar quoting (see <xref
103
linkend="sql-syntax-dollar-quoting">) for the string constant.
104
If you choose to use escape string syntax <literal>E''</>,
105
you must double any single quote marks (<literal>'</>) and backslashes
106
(<literal>\</>) used in the body of the function
107
(see <xref linkend="sql-syntax-strings">).
111
Arguments and results are handled as in any other Perl subroutine:
112
arguments are passed in <varname>@_</varname>, and a result value
113
is returned with <literal>return</> or as the last expression
114
evaluated in the function.
118
For example, a function returning the greater of two integer values
122
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
123
if ($_[0] > $_[1]) { return $_[0]; }
131
Arguments will be converted from the database's encoding to UTF-8
132
for use inside PL/Perl, and then converted from UTF-8 back to the
133
database encoding upon return.
138
If an SQL null value<indexterm><primary>null value</><secondary
139
sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
140
the argument value will appear as <quote>undefined</> in Perl. The
141
above function definition will not behave very nicely with null
142
inputs (in fact, it will act as though they are zeroes). We could
143
add <literal>STRICT</> to the function definition to make
144
<productname>PostgreSQL</productname> do something more reasonable:
145
if a null value is passed, the function will not be called at all,
146
but will just return a null result automatically. Alternatively,
147
we could check for undefined inputs in the function body. For
148
example, suppose that we wanted <function>perl_max</function> with
149
one null and one nonnull argument to return the nonnull argument,
150
rather than a null value:
153
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
155
if (not defined $x) {
156
return undef if not defined $y;
159
return $x if not defined $y;
160
return $x if $x > $y;
164
As shown above, to return an SQL null value from a PL/Perl
165
function, return an undefined value. This can be done whether the
166
function is strict or not.
170
Anything in a function argument that is not a reference is
171
a string, which is in the standard <productname>PostgreSQL</productname>
172
external text representation for the relevant data type. In the case of
173
ordinary numeric or text types, Perl will just do the right thing and
174
the programmer will normally not have to worry about it. However, in
175
other cases the argument will need to be converted into a form that is
176
more usable in Perl. For example, the <function>decode_bytea</function>
177
function can be used to convert an argument of
178
type <type>bytea</> into unescaped binary.
182
Similarly, values passed back to <productname>PostgreSQL</productname>
183
must be in the external text representation format. For example, the
184
<function>encode_bytea</function> function can be used to
185
escape binary data for a return value of type <type>bytea</>.
189
Perl can return <productname>PostgreSQL</productname> arrays as
190
references to Perl arrays. Here is an example:
193
CREATE OR REPLACE function returns_array()
194
RETURNS text[][] AS $$
195
return [['a"b','c,d'],['e\\f','g']];
198
select returns_array();
203
Perl passes <productname>PostgreSQL</productname> arrays as a blessed
204
PostgreSQL::InServer::ARRAY object. This object may be treated as an array
205
reference or a string, allowing for backward compatibility with Perl
206
code written for <productname>PostgreSQL</productname> versions below 9.1 to
210
CREATE OR REPLACE FUNCTION concat_array_elements(text[]) RETURNS TEXT AS $$
213
return undef if (!defined $arg);
215
# as an array reference
220
# also works as a string
226
SELECT concat_array_elements(ARRAY['PL','/','Perl']);
231
Multi-dimensional arrays are represented as references to
232
lower-dimensional arrays of references in a way common to every Perl
239
Composite-type arguments are passed to the function as references
240
to hashes. The keys of the hash are the attribute names of the
241
composite type. Here is an example:
244
CREATE TABLE employee (
250
CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
252
return $emp->{basesalary} + $emp->{bonus};
255
SELECT name, empcomp(employee.*) FROM employee;
260
A PL/Perl function can return a composite-type result using the same
261
approach: return a reference to a hash that has the required attributes.
265
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
267
CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
268
return {f2 => 'hello', f1 => 1, f3 => 'world'};
271
SELECT * FROM perl_row();
274
Any columns in the declared result data type that are not present in the
275
hash will be returned as null values.
279
PL/Perl functions can also return sets of either scalar or
280
composite types. Usually you'll want to return rows one at a
281
time, both to speed up startup time and to keep from queueing up
282
the entire result set in memory. You can do this with
283
<function>return_next</function> as illustrated below. Note that
284
after the last <function>return_next</function>, you must put
285
either <literal>return</literal> or (better) <literal>return
289
CREATE OR REPLACE FUNCTION perl_set_int(int)
290
RETURNS SETOF INTEGER AS $$
297
SELECT * FROM perl_set_int(5);
299
CREATE OR REPLACE FUNCTION perl_set()
300
RETURNS SETOF testrowperl AS $$
301
return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
302
return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
303
return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
308
For small result sets, you can return a reference to an array that
309
contains either scalars, references to arrays, or references to
310
hashes for simple types, array types, and composite types,
311
respectively. Here are some simple examples of returning the entire
312
result set as an array reference:
315
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
319
SELECT * FROM perl_set_int(5);
321
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
323
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
324
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
325
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
329
SELECT * FROM perl_set();
334
If you wish to use the <literal>strict</> pragma with your code you
335
have a few options. For temporary global use you can <command>SET</>
336
<literal>plperl.use_strict</literal> to true.
337
This will affect subsequent compilations of <application>PL/Perl</>
338
functions, but not functions already compiled in the current session.
339
For permanent global use you can set <literal>plperl.use_strict</literal>
340
to true in the <filename>postgresql.conf</filename> file.
344
For permanent use in specific functions you can simply put:
348
at the top of the function body.
352
The <literal>feature</> pragma is also available to <function>use</> if your Perl is version 5.10.0 or higher.
357
<sect1 id="plperl-data">
358
<title>Data Values in PL/Perl</title>
361
The argument values supplied to a PL/Perl function's code are
362
simply the input arguments converted to text form (just as if they
363
had been displayed by a <command>SELECT</command> statement).
364
Conversely, the <function>return</function> and <function>return_next</function>
365
commands will accept any string that is acceptable input format
366
for the function's declared return type.
370
<sect1 id="plperl-builtins">
371
<title>Built-in Functions</title>
373
<sect2 id="plperl-database">
374
<title>Database Access from PL/Perl</title>
377
Access to the database itself from your Perl function can be done
378
via the following functions:
384
<primary>spi_exec_query</primary>
385
<secondary>in PL/Perl</secondary>
388
<primary>spi_query</primary>
389
<secondary>in PL/Perl</secondary>
392
<primary>spi_fetchrow</primary>
393
<secondary>in PL/Perl</secondary>
396
<primary>spi_prepare</primary>
397
<secondary>in PL/Perl</secondary>
400
<primary>spi_exec_prepared</primary>
401
<secondary>in PL/Perl</secondary>
404
<primary>spi_query_prepared</primary>
405
<secondary>in PL/Perl</secondary>
408
<primary>spi_cursor_close</primary>
409
<secondary>in PL/Perl</secondary>
412
<primary>spi_freeplan</primary>
413
<secondary>in PL/Perl</secondary>
416
<term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
419
<literal>spi_exec_query</literal> executes an SQL command and
420
returns the entire row set as a reference to an array of hash
421
references. <emphasis>You should only use this command when you know
422
that the result set will be relatively small.</emphasis> Here is an
423
example of a query (<command>SELECT</command> command) with the
424
optional maximum number of rows:
427
$rv = spi_exec_query('SELECT * FROM my_table', 5);
429
This returns up to 5 rows from the table
430
<literal>my_table</literal>. If <literal>my_table</literal>
431
has a column <literal>my_column</literal>, you can get that
432
value from row <literal>$i</literal> of the result like this:
434
$foo = $rv->{rows}[$i]->{my_column};
436
The total number of rows returned from a <command>SELECT</command>
437
query can be accessed like this:
439
$nrows = $rv->{processed}
444
Here is an example using a different command type:
446
$query = "INSERT INTO my_table VALUES (1, 'test')";
447
$rv = spi_exec_query($query);
449
You can then access the command status (e.g.,
450
<literal>SPI_OK_INSERT</literal>) like this:
452
$res = $rv->{status};
454
To get the number of rows affected, do:
456
$nrows = $rv->{processed};
461
Here is a complete example:
468
INSERT INTO test (i, v) VALUES (1, 'first line');
469
INSERT INTO test (i, v) VALUES (2, 'second line');
470
INSERT INTO test (i, v) VALUES (3, 'third line');
471
INSERT INTO test (i, v) VALUES (4, 'immortal');
473
CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
474
my $rv = spi_exec_query('select i, v from test;');
475
my $status = $rv->{status};
476
my $nrows = $rv->{processed};
477
foreach my $rn (0 .. $nrows - 1) {
478
my $row = $rv->{rows}[$rn];
479
$row->{i} += 200 if defined($row->{i});
480
$row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
486
SELECT * FROM test_munge();
493
<term><literal><function>spi_query(<replaceable>command</replaceable>)</function></literal></term>
494
<term><literal><function>spi_fetchrow(<replaceable>cursor</replaceable>)</function></literal></term>
495
<term><literal><function>spi_cursor_close(<replaceable>cursor</replaceable>)</function></literal></term>
499
<literal>spi_query</literal> and <literal>spi_fetchrow</literal>
500
work together as a pair for row sets which might be large, or for cases
501
where you wish to return rows as they arrive.
502
<literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
503
<literal>spi_query</literal>. The following example illustrates how
504
you use them together:
507
CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
509
CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
510
use Digest::MD5 qw(md5_hex);
511
my $file = '/usr/share/dict/words';
513
elog(NOTICE, "opening file $file at $t" );
514
open my $fh, '<', $file # ooh, it's a file access!
515
or elog(ERROR, "cannot open $file for reading: $!");
516
my @words = <$fh>;
519
elog(NOTICE, "closed file $file at $t");
522
my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
523
while (defined ($row = spi_fetchrow($sth))) {
525
the_num => $row->{a},
526
the_text => md5_hex($words[rand @words])
532
SELECT * from lotsa_md5(500);
537
Normally, <function>spi_fetchrow</> should be repeated until it
538
returns <literal>undef</literal>, indicating that there are no more
539
rows to read. The cursor returned by <literal>spi_query</literal>
540
is automatically freed when
541
<function>spi_fetchrow</> returns <literal>undef</literal>.
542
If you do not wish to read all the rows, instead call
543
<function>spi_cursor_close</> to free the cursor.
544
Failure to do so will result in memory leaks.
551
<term><literal><function>spi_prepare(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</function></literal></term>
552
<term><literal><function>spi_query_prepared(<replaceable>plan</replaceable>, <replaceable>arguments</replaceable>)</function></literal></term>
553
<term><literal><function>spi_exec_prepared(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</function></literal></term>
554
<term><literal><function>spi_freeplan(<replaceable>plan</replaceable>)</function></literal></term>
558
<literal>spi_prepare</literal>, <literal>spi_query_prepared</literal>, <literal>spi_exec_prepared</literal>,
559
and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries.
560
<literal>spi_prepare</literal> accepts a query string with numbered argument placeholders ($1, $2, etc)
561
and a string list of argument types:
563
$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2',
566
Once a query plan is prepared by a call to <literal>spi_prepare</literal>, the plan can be used instead
567
of the string query, either in <literal>spi_exec_prepared</literal>, where the result is the same as returned
568
by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepared</literal> which returns a cursor
569
exactly as <literal>spi_query</literal> does, which can be later passed to <literal>spi_fetchrow</literal>.
570
The optional second parameter to <literal>spi_exec_prepared</literal> is a hash reference of attributes;
571
the only attribute currently supported is <literal>limit</literal>, which sets the maximum number of rows returned by a query.
575
The advantage of prepared queries is that is it possible to use one prepared plan for more
576
than one query execution. After the plan is not needed anymore, it can be freed with
577
<literal>spi_freeplan</literal>:
579
CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
580
$_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',
584
CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
585
return spi_exec_prepared(
588
)->{rows}->[0]->{now};
591
CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
592
spi_freeplan( $_SHARED{my_plan});
593
undef $_SHARED{my_plan};
597
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
600
add_time | add_time | add_time
601
------------+------------+------------
602
2005-12-10 | 2005-12-11 | 2005-12-12
604
Note that the parameter subscript in <literal>spi_prepare</literal> is defined via
605
$1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily
606
lead to hard-to-catch bugs.
610
Another example illustrates usage of an optional parameter in <literal>spi_exec_prepared</literal>:
612
CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address
613
FROM generate_series(1,3) AS id;
615
CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
616
$_SHARED{plan} = spi_prepare('SELECT * FROM hosts
617
WHERE address << $1', 'inet');
620
CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
621
return spi_exec_prepared(
628
CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
629
spi_freeplan($_SHARED{plan});
630
undef $_SHARED{plan};
633
SELECT init_hosts_query();
634
SELECT query_hosts('192.168.1.0/30');
635
SELECT release_hosts_query();
649
<sect2 id="plperl-utility-functions">
650
<title>Utility Functions in PL/Perl</title>
655
<primary>elog</primary>
656
<secondary>in PL/Perl</secondary>
659
<term><literal><function>elog(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</function></literal></term>
662
Emit a log or error message. Possible levels are
663
<literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
664
<literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
666
raises an error condition; if this is not trapped by the surrounding
667
Perl code, the error propagates out to the calling query, causing
668
the current transaction or subtransaction to be aborted. This
669
is effectively the same as the Perl <literal>die</> command.
670
The other levels only generate messages of different
672
Whether messages of a particular priority are reported to the client,
673
written to the server log, or both is controlled by the
674
<xref linkend="guc-log-min-messages"> and
675
<xref linkend="guc-client-min-messages"> configuration
676
variables. See <xref linkend="runtime-config"> for more
684
<primary>quote_literal</primary>
685
<secondary>in PL/Perl</secondary>
688
<term><literal><function>quote_literal(<replaceable>string</replaceable>)</function></literal></term>
691
Return the given string suitably quoted to be used as a string literal in an SQL
692
statement string. Embedded single-quotes and backslashes are properly doubled.
693
Note that <function>quote_literal</> returns undef on undef input; if the argument
694
might be undef, <function>quote_nullable</> is often more suitable.
701
<primary>quote_nullable</primary>
702
<secondary>in PL/Perl</secondary>
705
<term><literal><function>quote_nullable(<replaceable>string</replaceable>)</function></literal></term>
708
Return the given string suitably quoted to be used as a string literal in an SQL
709
statement string; or, if the argument is undef, return the unquoted string "NULL".
710
Embedded single-quotes and backslashes are properly doubled.
717
<primary>quote_ident</primary>
718
<secondary>in PL/Perl</secondary>
721
<term><literal><function>quote_ident(<replaceable>string</replaceable>)</function></literal></term>
724
Return the given string suitably quoted to be used as an identifier in
725
an SQL statement string. Quotes are added only if necessary (i.e., if
726
the string contains non-identifier characters or would be case-folded).
727
Embedded quotes are properly doubled.
734
<primary>decode_bytea</primary>
735
<secondary>in PL/Perl</secondary>
738
<term><literal><function>decode_bytea(<replaceable>string</replaceable>)</function></literal></term>
741
Return the unescaped binary data represented by the contents of the given string,
742
which should be <type>bytea</type> encoded.
749
<primary>encode_bytea</primary>
750
<secondary>in PL/Perl</secondary>
753
<term><literal><function>encode_bytea(<replaceable>string</replaceable>)</function></literal></term>
756
Return the <type>bytea</type> encoded form of the binary data contents of the given string.
763
<primary>encode_array_literal</primary>
764
<secondary>in PL/Perl</secondary>
767
<term><literal><function>encode_array_literal(<replaceable>array</replaceable>)</function></literal></term>
768
<term><literal><function>encode_array_literal(<replaceable>array</replaceable>, <replaceable>delimiter</replaceable>)</function></literal></term>
771
Returns the contents of the referenced array as a string in array literal format
772
(see <xref linkend="arrays-input">).
773
Returns the argument value unaltered if it's not a reference to an array.
774
The delimiter used between elements of the array literal defaults to "<literal>, </literal>"
775
if a delimiter is not specified or is undef.
782
<primary>encode_typed_literal</primary>
783
<secondary>in PL/Perl</secondary>
786
<term><literal><function>encode_typed_literal(<replaceable>value</replaceable>, <replaceable>typename</replaceable>)</function></literal></term>
789
Converts a Perl variable to the value of the data type passed as a
790
second argument and returns a string representation of this value.
791
Correctly handles nested arrays and values of composite types.
798
<primary>encode_array_constructor</primary>
799
<secondary>in PL/Perl</secondary>
802
<term><literal><function>encode_array_constructor(<replaceable>array</replaceable>)</function></literal></term>
805
Returns the contents of the referenced array as a string in array constructor format
806
(see <xref linkend="sql-syntax-array-constructors">).
807
Individual values are quoted using <function>quote_nullable</function>.
808
Returns the argument value, quoted using <function>quote_nullable</function>,
809
if it's not a reference to an array.
816
<primary>looks_like_number</primary>
817
<secondary>in PL/Perl</secondary>
820
<term><literal><function>looks_like_number(<replaceable>string</replaceable>)</function></literal></term>
823
Returns a true value if the content of the given string looks like a
824
number, according to Perl, returns false otherwise.
825
Returns undef if the argument is undef. Leading and trailing space is
826
ignored. <literal>Inf</> and <literal>Infinity</> are regarded as numbers.
833
<primary>is_array_ref</primary>
834
<secondary>in PL/Perl</secondary>
837
<term><literal><function>is_array_ref(<replaceable>argument</replaceable>)</function></literal></term>
840
Returns a true value if the given argument may be treated as an
841
array reference, that is, if ref of the argument is <literal>ARRAY</> or
842
<literal>PostgreSQL::InServer::ARRAY</>. Returns false otherwise.
851
<sect1 id="plperl-global">
852
<title>Global Values in PL/Perl</title>
855
You can use the global hash <varname>%_SHARED</varname> to store
856
data, including code references, between function calls for the
857
lifetime of the current session.
861
Here is a simple example for shared data:
863
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
864
if ($_SHARED{$_[0]} = $_[1]) {
867
return "cannot set shared variable $_[0] to $_[1]";
871
CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
872
return $_SHARED{$_[0]};
875
SELECT set_var('sample', 'Hello, PL/Perl! How''s tricks?');
876
SELECT get_var('sample');
881
Here is a slightly more complicated example using a code reference:
884
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
885
$_SHARED{myquote} = sub {
887
$arg =~ s/(['\\])/\\$1/g;
892
SELECT myfuncs(); /* initializes the function */
894
/* Set up a function that uses the quote function */
896
CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
897
my $text_to_quote = shift;
898
my $qfunc = $_SHARED{myquote};
899
return &$qfunc($text_to_quote);
903
(You could have replaced the above with the one-liner
904
<literal>return $_SHARED{myquote}->($_[0]);</literal>
905
at the expense of readability.)
909
For security reasons, PL/Perl executes functions called by any one SQL role
910
in a separate Perl interpreter for that role. This prevents accidental or
911
malicious interference by one user with the behavior of another user's
912
PL/Perl functions. Each such interpreter has its own value of the
913
<varname>%_SHARED</varname> variable and other global state. Thus, two
914
PL/Perl functions will share the same value of <varname>%_SHARED</varname>
915
if and only if they are executed by the same SQL role. In an application
916
wherein a single session executes code under multiple SQL roles (via
917
<literal>SECURITY DEFINER</> functions, use of <command>SET ROLE</>, etc)
918
you may need to take explicit steps to ensure that PL/Perl functions can
919
share data via <varname>%_SHARED</varname>. To do that, make sure that
920
functions that should communicate are owned by the same user, and mark
921
them <literal>SECURITY DEFINER</>. You must of course take care that
922
such functions can't be used to do anything unintended.
926
<sect1 id="plperl-trusted">
927
<title>Trusted and Untrusted PL/Perl</title>
929
<indexterm zone="plperl-trusted">
930
<primary>trusted</primary>
931
<secondary>PL/Perl</secondary>
935
Normally, PL/Perl is installed as a <quote>trusted</> programming
936
language named <literal>plperl</>. In this setup, certain Perl
937
operations are disabled to preserve security. In general, the
938
operations that are restricted are those that interact with the
939
environment. This includes file handle operations,
940
<literal>require</literal>, and <literal>use</literal> (for
941
external modules). There is no way to access internals of the
942
database server process or to gain OS-level access with the
943
permissions of the server process,
944
as a C function can do. Thus, any unprivileged database user can
945
be permitted to use this language.
949
Here is an example of a function that will not work because file
950
system operations are not allowed for security reasons:
952
CREATE FUNCTION badfunc() RETURNS integer AS $$
953
my $tmpfile = "/tmp/badfile";
954
open my $fh, '>', $tmpfile
955
or elog(ERROR, qq{could not open the file "$tmpfile": $!});
956
print $fh "Testing writing to a file\n";
957
close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
961
The creation of this function will fail as its use of a forbidden
962
operation will be caught by the validator.
966
Sometimes it is desirable to write Perl functions that are not
967
restricted. For example, one might want a Perl function that sends
968
mail. To handle these cases, PL/Perl can also be installed as an
969
<quote>untrusted</> language (usually called
970
<application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
971
In this case the full Perl language is available. When installing the
972
language, the language name <literal>plperlu</literal> will select
973
the untrusted PL/Perl variant.
977
The writer of a <application>PL/PerlU</> function must take care that the function
978
cannot be used to do anything unwanted, since it will be able to do
979
anything that could be done by a user logged in as the database
980
administrator. Note that the database system allows only database
981
superusers to create functions in untrusted languages.
985
If the above function was created by a superuser using the language
986
<literal>plperlu</>, execution would succeed.
990
In the same way, anonymous code blocks written in Perl can use
991
restricted operations if the language is specified as
992
<literal>plperlu</> rather than <literal>plperl</>, but the caller
998
While <application>PL/Perl</> functions run in a separate Perl
999
interpreter for each SQL role, all <application>PL/PerlU</> functions
1000
executed in a given session run in a single Perl interpreter (which is
1001
not any of the ones used for <application>PL/Perl</> functions).
1002
This allows <application>PL/PerlU</> functions to share data freely,
1003
but no communication can occur between <application>PL/Perl</> and
1004
<application>PL/PerlU</> functions.
1010
Perl cannot support multiple interpreters within one process unless
1011
it was built with the appropriate flags, namely either
1012
<literal>usemultiplicity</> or <literal>useithreads</>.
1013
(<literal>usemultiplicity</> is preferred unless you actually need
1014
to use threads. For more details, see the
1015
<citerefentry><refentrytitle>perlembed</></citerefentry> man page.)
1016
If <application>PL/Perl</> is used with a copy of Perl that was not built
1017
this way, then it is only possible to have one Perl interpreter per
1018
session, and so any one session can only execute either
1019
<application>PL/PerlU</> functions, or <application>PL/Perl</> functions
1020
that are all called by the same SQL role.
1026
<sect1 id="plperl-triggers">
1027
<title>PL/Perl Triggers</title>
1030
PL/Perl can be used to write trigger functions. In a trigger function,
1031
the hash reference <varname>$_TD</varname> contains information about the
1032
current trigger event. <varname>$_TD</> is a global variable,
1033
which gets a separate local value for each invocation of the trigger.
1034
The fields of the <varname>$_TD</varname> hash reference are:
1038
<term><literal>$_TD->{new}{foo}</literal></term>
1041
<literal>NEW</literal> value of column <literal>foo</literal>
1047
<term><literal>$_TD->{old}{foo}</literal></term>
1050
<literal>OLD</literal> value of column <literal>foo</literal>
1056
<term><literal>$_TD->{name}</literal></term>
1059
Name of the trigger being called
1065
<term><literal>$_TD->{event}</literal></term>
1068
Trigger event: <literal>INSERT</>, <literal>UPDATE</>,
1069
<literal>DELETE</>, <literal>TRUNCATE</>, or <literal>UNKNOWN</>
1075
<term><literal>$_TD->{when}</literal></term>
1078
When the trigger was called: <literal>BEFORE</literal>,
1079
<literal>AFTER</literal>, <literal>INSTEAD OF</literal>, or
1080
<literal>UNKNOWN</literal>
1086
<term><literal>$_TD->{level}</literal></term>
1089
The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
1095
<term><literal>$_TD->{relid}</literal></term>
1098
OID of the table on which the trigger fired
1104
<term><literal>$_TD->{table_name}</literal></term>
1107
Name of the table on which the trigger fired
1113
<term><literal>$_TD->{relname}</literal></term>
1116
Name of the table on which the trigger fired. This has been deprecated,
1117
and could be removed in a future release.
1118
Please use $_TD->{table_name} instead.
1124
<term><literal>$_TD->{table_schema}</literal></term>
1127
Name of the schema in which the table on which the trigger fired, is
1133
<term><literal>$_TD->{argc}</literal></term>
1136
Number of arguments of the trigger function
1142
<term><literal>@{$_TD->{args}}</literal></term>
1145
Arguments of the trigger function. Does not exist if <literal>$_TD->{argc}</literal> is 0.
1154
Row-level triggers can return one of the following:
1158
<term><literal>return;</literal></term>
1161
Execute the operation
1167
<term><literal>"SKIP"</literal></term>
1170
Don't execute the operation
1176
<term><literal>"MODIFY"</literal></term>
1179
Indicates that the <literal>NEW</literal> row was modified by
1180
the trigger function
1188
Here is an example of a trigger function, illustrating some of the
1196
CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
1197
if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
1198
return "SKIP"; # skip INSERT/UPDATE command
1199
} elsif ($_TD->{new}{v} ne "immortal") {
1200
$_TD->{new}{v} .= "(modified by trigger)";
1201
return "MODIFY"; # modify row and execute INSERT/UPDATE command
1203
return; # execute INSERT/UPDATE command
1207
CREATE TRIGGER test_valid_id_trig
1208
BEFORE INSERT OR UPDATE ON test
1209
FOR EACH ROW EXECUTE PROCEDURE valid_id();
1214
<sect1 id="plperl-under-the-hood">
1215
<title>PL/Perl Under the Hood</title>
1217
<sect2 id="plperl-config">
1218
<title>Configuration</title>
1221
This section lists configuration parameters that affect <application>PL/Perl</>.
1226
<varlistentry id="guc-plperl-on-init" xreflabel="plperl.on_init">
1227
<term><varname>plperl.on_init</varname> (<type>string</type>)</term>
1229
<primary><varname>plperl.on_init</> configuration parameter</primary>
1233
Specifies Perl code to be executed when a Perl interpreter is first
1234
initialized, before it is specialized for use by <literal>plperl</> or
1235
<literal>plperlu</>.
1236
The SPI functions are not available when this code is executed.
1237
If the code fails with an error it will abort the initialization of
1238
the interpreter and propagate out to the calling query, causing the
1239
current transaction or subtransaction to be aborted.
1242
The Perl code is limited to a single string. Longer code can be placed
1243
into a module and loaded by the <literal>on_init</> string.
1246
plperl.on_init = 'require "plperlinit.pl"'
1247
plperl.on_init = 'use lib "/my/app"; use MyApp::PgInit;'
1251
Any modules loaded by <literal>plperl.on_init</>, either directly or
1252
indirectly, will be available for use by <literal>plperl</>. This may
1253
create a security risk. To see what modules have been loaded you can use:
1255
DO 'elog(WARNING, join ", ", sort keys %INC)' LANGUAGE plperl;
1259
Initialization will happen in the postmaster if the plperl library is
1260
included in <xref linkend="guc-shared-preload-libraries">, in which
1261
case extra consideration should be given to the risk of destabilizing
1262
the postmaster. The principal reason for making use of this feature
1263
is that Perl modules loaded by <literal>plperl.on_init</> need be
1264
loaded only at postmaster start, and will be instantly available
1265
without loading overhead in individual database sessions. However,
1266
keep in mind that the overhead is avoided only for the first Perl
1267
interpreter used by a database session — either PL/PerlU, or
1268
PL/Perl for the first SQL role that calls a PL/Perl function. Any
1269
additional Perl interpreters created in a database session will have
1270
to execute <literal>plperl.on_init</> afresh. Also, on Windows there
1271
will be no savings whatsoever from preloading, since the Perl
1272
interpreter created in the postmaster process does not propagate to
1276
This parameter can only be set in the <filename>postgresql.conf</filename> file or on the server command line.
1281
<varlistentry id="guc-plperl-on-plperl-init" xreflabel="plperl.on_plperl_init">
1282
<term><varname>plperl.on_plperl_init</varname> (<type>string</type>)</term>
1283
<term><varname>plperl.on_plperlu_init</varname> (<type>string</type>)</term>
1285
<primary><varname>plperl.on_plperl_init</> configuration parameter</primary>
1288
<primary><varname>plperl.on_plperlu_init</> configuration parameter</primary>
1292
These parameters specify Perl code to be executed when a Perl
1293
interpreter is specialized for <literal>plperl</> or
1294
<literal>plperlu</> respectively. This will happen when a PL/Perl or
1295
PL/PerlU function is first executed in a database session, or when
1296
an additional interpreter has to be created because the other language
1297
is called or a PL/Perl function is called by a new SQL role. This
1298
follows any initialization done by <literal>plperl.on_init</>.
1299
The SPI functions are not available when this code is executed.
1300
The Perl code in <literal>plperl.on_plperl_init</> is executed after
1301
<quote>locking down</> the interpreter, and thus it can only perform
1305
If the code fails with an error it will abort the initialization and
1306
propagate out to the calling query, causing the current transaction or
1307
subtransaction to be aborted. Any actions already done within Perl
1308
won't be undone; however, that interpreter won't be used again.
1309
If the language is used again the initialization will be attempted
1310
again within a fresh Perl interpreter.
1313
Only superusers can change these settings. Although these settings
1314
can be changed within a session, such changes will not affect Perl
1315
interpreters that have already been used to execute functions.
1320
<varlistentry id="guc-plperl-use-strict" xreflabel="plperl.use_strict">
1321
<term><varname>plperl.use_strict</varname> (<type>boolean</type>)</term>
1323
<primary><varname>plperl.use_strict</> configuration parameter</primary>
1327
When set true subsequent compilations of PL/Perl functions will have
1328
the <literal>strict</> pragma enabled. This parameter does not affect
1329
functions already compiled in the current session.
1337
<sect2 id="plperl-missing">
1338
<title>Limitations and Missing Features</title>
1341
The following features are currently missing from PL/Perl, but they
1342
would make welcome contributions.
1347
PL/Perl functions cannot call each other directly.
1353
SPI is not yet fully implemented.
1359
If you are fetching very large data sets using
1360
<literal>spi_exec_query</literal>, you should be aware that
1361
these will all go into memory. You can avoid this by using
1362
<literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
1363
illustrated earlier.
1366
A similar problem occurs if a set-returning function passes a
1367
large set of rows back to PostgreSQL via <literal>return</literal>. You
1368
can avoid this problem too by instead using
1369
<literal>return_next</literal> for each row returned, as shown
1376
When a session ends normally, not due to a fatal error, any
1377
<literal>END</> blocks that have been defined are executed.
1378
Currently no other actions are performed. Specifically,
1379
file handles are not automatically flushed and objects are
1380
not automatically destroyed.
added
removed
doc/src/sgml/plperl.sgml
