2 $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.49 2005/11/04 23:14:00 petere 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
19 <ulink url="http://www.perl.com">Perl programming language</ulink>.
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
48 <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
52 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
53 # PL/Perl function body
56 The body of the function is ordinary Perl code. In fact, the PL/Perl
57 glue code wraps it inside a Perl subroutine. A PL/Perl function must
58 always return a scalar value. You can return more complex structures
59 (arrays, records, and sets) by returning a reference, as discussed below.
65 The use of named nested subroutines is dangerous in Perl, especially if
66 they refer to lexical variables in the enclosing scope. Because a PL/Perl
67 function is wrapped in a subroutine, any named subroutine you create will
68 be nested. In general, it is far safer to create anonymous subroutines
69 which you call via a coderef. See the <literal>perldiag</literal>
70 man page for more details.
75 The syntax of the <command>CREATE FUNCTION</command> command requires
76 the function body to be written as a string constant. It is usually
77 most convenient to use dollar quoting (see <xref
78 linkend="sql-syntax-dollar-quoting">) for the string constant.
79 If you choose to use regular single-quoted string constant syntax,
80 you must escape single quote marks (<literal>'</>) and backslashes
81 (<literal>\</>) used in the body of the function, typically by
82 doubling them (see <xref linkend="sql-syntax-strings">).
86 Arguments and results are handled as in any other Perl subroutine:
87 arguments are passed in <varname>@_</varname>, and a result value
88 is returned with <literal>return</> or as the last expression
89 evaluated in the function.
93 For example, a function returning the greater of two integer values
97 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
98 if ($_[0] > $_[1]) { return $_[0]; }
105 If an SQL null value<indexterm><primary>null value</><secondary
106 sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
107 the argument value will appear as <quote>undefined</> in Perl. The
108 above function definition will not behave very nicely with null
109 inputs (in fact, it will act as though they are zeroes). We could
110 add <literal>STRICT</> to the function definition to make
111 <productname>PostgreSQL</productname> do something more reasonable:
112 if a null value is passed, the function will not be called at all,
113 but will just return a null result automatically. Alternatively,
114 we could check for undefined inputs in the function body. For
115 example, suppose that we wanted <function>perl_max</function> with
116 one null and one nonnull argument to return the nonnull argument,
117 rather than a null value:
120 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
123 if (! defined $y) { return undef; }
126 if (! defined $y) { return $x; }
127 if ($x > $y) { return $x; }
131 As shown above, to return an SQL null value from a PL/Perl
132 function, return an undefined value. This can be done whether the
133 function is strict or not.
137 Perl can return <productname>PostgreSQL</productname> arrays as
138 references to Perl arrays. Here is an example:
141 CREATE OR REPLACE function returns_array()
142 RETURNS text[][] AS $$
143 return [['a"b','c,d'],['e\\f','g']];
146 select returns_array();
151 Composite-type arguments are passed to the function as references
152 to hashes. The keys of the hash are the attribute names of the
153 composite type. Here is an example:
156 CREATE TABLE employee (
162 CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
164 return $emp->{basesalary} + $emp->{bonus};
167 SELECT name, empcomp(employee.*) FROM employee;
172 A PL/Perl function can return a composite-type result using the same
173 approach: return a reference to a hash that has the required attributes.
177 CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
179 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
180 return {f2 => 'hello', f1 => 1, f3 => 'world'};
183 SELECT * FROM perl_row();
186 Any columns in the declared result data type that are not present in the
187 hash will be returned as NULLs.
191 PL/Perl functions can also return sets of either scalar or
192 composite types. Usually you'll want to return rows one at a
193 time, both to speed up startup time and to keep from queueing up
194 the entire result set in memory. You can do this with
195 <function>return_next</function> as illustrated below. Note that
196 after the last <function>return_next</function>, you must put
197 either <literal>return</literal> or (better) <literal>return
201 CREATE OR REPLACE FUNCTION perl_set_int(int)
202 RETURNS SETOF INTEGER AS $$
209 SELECT * FROM perl_set_int(5);
211 CREATE OR REPLACE FUNCTION perl_set()
212 RETURNS SETOF testrowperl AS $$
213 return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
214 return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
215 return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
220 For small result sets, you can return a reference to an array that
221 contains either scalars, references to arrays, or references to
222 hashes for simple types, array types, and composite types,
223 respectively. Here are some simple examples of returning the entire
224 result set as an array reference:
227 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
231 SELECT * FROM perl_set_int(5);
233 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
235 { f1 => 1, f2 => 'Hello', f3 => 'World' },
236 { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
237 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
241 SELECT * FROM perl_set();
247 <application>PL/Perl</> does not currently have full support for
248 domain types: it treats a domain the same as the underlying scalar
249 type. This means that constraints associated with the domain will
250 not be enforced. This is not an issue for function arguments, but
251 it is a hazard if you declare a <application>PL/Perl</> function
252 as returning a domain type.
256 If you wish to use the <literal>strict</> pragma with your code,
257 the easiest way to do so is to <command>SET</>
258 <literal>plperl.use_strict</literal> to true. This parameter affects
259 subsequent compilations of <application>PL/Perl</> functions, but not
260 functions already compiled in the current session. To set the
261 parameter before <application>PL/Perl</> has been loaded, it is
262 necessary to have added <quote><literal>plperl</></> to the <xref
263 linkend="guc-custom-variable-classes"> list in
264 <filename>postgresql.conf</filename>.
268 Another way to use the <literal>strict</> pragma is to put
272 in the function body. But this only works in <application>PL/PerlU</>
273 functions, since <literal>use</> is not a trusted operation. In
274 <application>PL/Perl</> functions you can instead do
276 BEGIN { strict->import(); }
281 <sect1 id="plperl-database">
282 <title>Database Access from PL/Perl</title>
285 Access to the database itself from your Perl function can be done
286 via the function <function>spi_exec_query</function> described
287 below, or via an experimental module
288 <ulink url="http://www.cpan.org/modules/by-module/DBD/APILOS/">
289 <literal>DBD::PgSPI</literal></ulink>
290 (also available at <ulink url="http://www.cpan.org/SITES.html">
291 <acronym>CPAN mirror sites</></ulink>). This module makes available a
292 <acronym>DBI</>-compliant database-handle named
293 <varname>$pg_dbh</varname> that can be used to perform queries with
294 normal <acronym>DBI</>
295 syntax.<indexterm><primary>DBI</></indexterm>
299 PL/Perl provides three additional Perl commands:
304 <primary>spi_exec_query</primary>
305 <secondary>in PL/Perl</secondary>
308 <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
309 <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
310 <term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term>
311 <term><literal><function>spi_fetchrow</>(<replaceable>command</replaceable>)</literal></term>
315 <literal>spi_exec_query</literal> executes an SQL command and
316 returns the entire row set as a reference to an array of hash
317 references. <emphasis>You should only use this command when you know
318 that the result set will be relatively small.</emphasis> Here is an
319 example of a query (<command>SELECT</command> command) with the
320 optional maximum number of rows:
323 $rv = spi_exec_query('SELECT * FROM my_table', 5);
325 This returns up to 5 rows from the table
326 <literal>my_table</literal>. If <literal>my_table</literal>
327 has a column <literal>my_column</literal>, you can get that
328 value from row <literal>$i</literal> of the result like this:
330 $foo = $rv->{rows}[$i]->{my_column};
332 The total number of rows returned from a <command>SELECT</command>
333 query can be accessed like this:
335 $nrows = $rv->{processed}
340 Here is an example using a different command type:
342 $query = "INSERT INTO my_table VALUES (1, 'test')";
343 $rv = spi_exec_query($query);
345 You can then access the command status (e.g.,
346 <literal>SPI_OK_INSERT</literal>) like this:
348 $res = $rv->{status};
350 To get the number of rows affected, do:
352 $nrows = $rv->{processed};
357 Here is a complete example:
364 INSERT INTO test (i, v) VALUES (1, 'first line');
365 INSERT INTO test (i, v) VALUES (2, 'second line');
366 INSERT INTO test (i, v) VALUES (3, 'third line');
367 INSERT INTO test (i, v) VALUES (4, 'immortal');
369 CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
370 my $rv = spi_exec_query('select i, v from test;');
371 my $status = $rv->{status};
372 my $nrows = $rv->{processed};
373 foreach my $rn (0 .. $nrows - 1) {
374 my $row = $rv->{rows}[$rn];
375 $row->{i} += 200 if defined($row->{i});
376 $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
382 SELECT * FROM test_munge();
386 <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
387 work together as a pair for row sets which may be large, or for cases
388 where you wish to return rows as they arrive.
389 <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
390 <literal>spi_query</literal>. The following example illustrates how
391 you use them together:
394 CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
396 CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
397 use Digest::MD5 qw(md5_hex);
398 my $file = '/usr/share/dict/words';
400 elog(NOTICE, "opening file $file at $t" );
401 open my $fh, '<', $file # ooh, it's a file access!
402 or elog(ERROR, "Can't open $file for reading: $!");
403 my @words = <$fh>;
406 elog(NOTICE, "closed file $file at $t");
409 my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
410 while (defined ($row = spi_fetchrow($sth))) {
412 the_num => $row->{a},
413 the_text => md5_hex($words[rand @words])
419 SELECT * from lotsa_md5(500);
428 <primary>elog</primary>
429 <secondary>in PL/Perl</secondary>
432 <term><literal><function>elog</>(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</literal></term>
435 Emit a log or error message. Possible levels are
436 <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
437 <literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
439 raises an error condition; if this is not trapped by the surrounding
440 Perl code, the error propagates out to the calling query, causing
441 the current transaction or subtransaction to be aborted. This
442 is effectively the same as the Perl <literal>die</> command.
443 The other levels only generate messages of different
445 Whether messages of a particular priority are reported to the client,
446 written to the server log, or both is controlled by the
447 <xref linkend="guc-log-min-messages"> and
448 <xref linkend="guc-client-min-messages"> configuration
449 variables. See <xref linkend="runtime-config"> for more
458 <sect1 id="plperl-data">
459 <title>Data Values in PL/Perl</title>
462 The argument values supplied to a PL/Perl function's code are
463 simply the input arguments converted to text form (just as if they
464 had been displayed by a <command>SELECT</command> statement).
465 Conversely, the <literal>return</> command will accept any string
466 that is acceptable input format for the function's declared return
467 type. So, within the PL/Perl function,
468 all values are just text strings.
472 <sect1 id="plperl-global">
473 <title>Global Values in PL/Perl</title>
476 You can use the global hash <varname>%_SHARED</varname> to store
477 data, including code references, between function calls for the
478 lifetime of the current session.
482 Here is a simple example for shared data:
484 CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
485 if ($_SHARED{$_[0]} = $_[1]) {
488 return "can't set shared variable $_[0] to $_[1]";
492 CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
493 return $_SHARED{$_[0]};
496 SELECT set_var('sample', 'Hello, PL/Perl! How's tricks?');
497 SELECT get_var('sample');
502 Here is a slightly more complicated example using a code reference:
505 CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
506 $_SHARED{myquote} = sub {
508 $arg =~ s/(['\\])/\\$1/g;
513 SELECT myfuncs(); /* initializes the function */
515 /* Set up a function that uses the quote function */
517 CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
518 my $text_to_quote = shift;
519 my $qfunc = $_SHARED{myquote};
520 return &$qfunc($text_to_quote);
524 (You could have replaced the above with the one-liner
525 <literal>return $_SHARED{myquote}->($_[0]);</literal>
526 at the expense of readability.)
530 <sect1 id="plperl-trusted">
531 <title>Trusted and Untrusted PL/Perl</title>
533 <indexterm zone="plperl-trusted">
534 <primary>trusted</primary>
535 <secondary>PL/Perl</secondary>
539 Normally, PL/Perl is installed as a <quote>trusted</> programming
540 language named <literal>plperl</>. In this setup, certain Perl
541 operations are disabled to preserve security. In general, the
542 operations that are restricted are those that interact with the
543 environment. This includes file handle operations,
544 <literal>require</literal>, and <literal>use</literal> (for
545 external modules). There is no way to access internals of the
546 database server process or to gain OS-level access with the
547 permissions of the server process,
548 as a C function can do. Thus, any unprivileged database user may
549 be permitted to use this language.
553 Here is an example of a function that will not work because file
554 system operations are not allowed for security reasons:
556 CREATE FUNCTION badfunc() RETURNS integer AS $$
557 my $tmpfile = "/tmp/badfile";
558 open my $fh, '>', $tmpfile
559 or elog(ERROR, qq{Could not open the file "$tmpfile": $!});
560 print $fh "Testing writing to a file\n";
561 close $fh or elog(ERROR, qq{Could not close the file "$tmpfile": $!});
565 The creation of this function will fail as its use of a forbidden
566 operation will be be caught by the validator.
570 Sometimes it is desirable to write Perl functions that are not
571 restricted. For example, one might want a Perl function that sends
572 mail. To handle these cases, PL/Perl can also be installed as an
573 <quote>untrusted</> language (usually called
574 <application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
575 In this case the full Perl language is available. If the
576 <command>createlang</command> program is used to install the
577 language, the language name <literal>plperlu</literal> will select
578 the untrusted PL/Perl variant.
582 The writer of a <application>PL/PerlU</> function must take care that the function
583 cannot be used to do anything unwanted, since it will be able to do
584 anything that could be done by a user logged in as the database
585 administrator. Note that the database system allows only database
586 superusers to create functions in untrusted languages.
590 If the above function was created by a superuser using the language
591 <literal>plperlu</>, execution would succeed.
595 <sect1 id="plperl-triggers">
596 <title>PL/Perl Triggers</title>
599 PL/Perl can be used to write trigger functions. In a trigger function,
600 the hash reference <varname>$_TD</varname> contains information about the
601 current trigger event. The fields of the <varname>$_TD</varname> hash
606 <term><literal>$_TD->{new}{foo}</literal></term>
609 <literal>NEW</literal> value of column <literal>foo</literal>
615 <term><literal>$_TD->{old}{foo}</literal></term>
618 <literal>OLD</literal> value of column <literal>foo</literal>
624 <term><literal>$_TD->{name}</literal></term>
627 Name of the trigger being called
633 <term><literal>$_TD->{event}</literal></term>
636 Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
642 <term><literal>$_TD->{when}</literal></term>
645 When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
651 <term><literal>$_TD->{level}</literal></term>
654 The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
660 <term><literal>$_TD->{relid}</literal></term>
663 OID of the table on which the trigger fired
669 <term><literal>$_TD->{relname}</literal></term>
672 Name of the table on which the trigger fired
678 <term><literal>$_TD->{argc}</literal></term>
681 Number of arguments of the trigger function
687 <term><literal>@{$_TD->{args}}</literal></term>
690 Arguments of the trigger function. Does not exist if <literal>$_TD->{argc}</literal> is 0.
699 Triggers can return one of the following:
703 <term><literal>return;</literal></term>
706 Execute the statement
712 <term><literal>"SKIP"</literal></term>
715 Don't execute the statement
721 <term><literal>"MODIFY"</literal></term>
724 Indicates that the <literal>NEW</literal> row was modified by
733 Here is an example of a trigger function, illustrating some of the
741 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
742 if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
743 return "SKIP"; # skip INSERT/UPDATE command
744 } elsif ($_TD->{new}{v} ne "immortal") {
745 $_TD->{new}{v} .= "(modified by trigger)";
746 return "MODIFY"; # modify row and execute INSERT/UPDATE command
748 return; # execute INSERT/UPDATE command
752 CREATE TRIGGER test_valid_id_trig
753 BEFORE INSERT OR UPDATE ON test
754 FOR EACH ROW EXECUTE PROCEDURE valid_id();
759 <sect1 id="plperl-missing">
760 <title>Limitations and Missing Features</title>
763 The following features are currently missing from PL/Perl, but they
764 would make welcome contributions.
769 PL/Perl functions cannot call each other directly (because they
770 are anonymous subroutines inside Perl).
776 SPI is not yet fully implemented.
782 If you are fetching very large data sets using
783 <literal>spi_exec_query</literal>, you should be aware that
784 these will all go into memory. You can avoid this by using
785 <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
789 A similar problem occurs if a set-returning function passes a
790 large set of rows back to PostgreSQL via <literal>return</literal>. You
791 can avoid this problem too by instead using
792 <literal>return_next</literal> for each row returned, as shown
803 <!-- Keep this comment at the end of the file
808 sgml-minimize-attributes:nil
809 sgml-always-quote-attributes:t
812 sgml-parent-document:nil
813 sgml-default-dtd-file:"./reference.ced"
814 sgml-exposed-tags:nil
815 sgml-local-catalogs:("/usr/lib/sgml/catalog")
816 sgml-local-ecat-files:nil