2 $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.47 2005/10/18 22:53:54 adunstan 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 rowset 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 rowsets 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 open(TEMP, ">/tmp/badfile");
558 print TEMP "Gotcha!\n";
562 The creation of the function will succeed, but executing it will not.
566 Sometimes it is desirable to write Perl functions that are not
567 restricted. For example, one might want a Perl function that sends
568 mail. To handle these cases, PL/Perl can also be installed as an
569 <quote>untrusted</> language (usually called
570 <application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
571 In this case the full Perl language is available. If the
572 <command>createlang</command> program is used to install the
573 language, the language name <literal>plperlu</literal> will select
574 the untrusted PL/Perl variant.
578 The writer of a <application>PL/PerlU</> function must take care that the function
579 cannot be used to do anything unwanted, since it will be able to do
580 anything that could be done by a user logged in as the database
581 administrator. Note that the database system allows only database
582 superusers to create functions in untrusted languages.
586 If the above function was created by a superuser using the language
587 <literal>plperlu</>, execution would succeed.
591 <sect1 id="plperl-triggers">
592 <title>PL/Perl Triggers</title>
595 PL/Perl can be used to write trigger functions. In a trigger function,
596 the hash reference <varname>$_TD</varname> contains information about the
597 current trigger event. The fields of the <varname>$_TD</varname> hash
602 <term><literal>$_TD->{new}{foo}</literal></term>
605 <literal>NEW</literal> value of column <literal>foo</literal>
611 <term><literal>$_TD->{old}{foo}</literal></term>
614 <literal>OLD</literal> value of column <literal>foo</literal>
620 <term><literal>$_TD->{name}</literal></term>
623 Name of the trigger being called
629 <term><literal>$_TD->{event}</literal></term>
632 Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
638 <term><literal>$_TD->{when}</literal></term>
641 When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
647 <term><literal>$_TD->{level}</literal></term>
650 The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
656 <term><literal>$_TD->{relid}</literal></term>
659 OID of the table on which the trigger fired
665 <term><literal>$_TD->{relname}</literal></term>
668 Name of the table on which the trigger fired
674 <term><literal>$_TD->{argc}</literal></term>
677 Number of arguments of the trigger function
683 <term><literal>@{$_TD->{args}}</literal></term>
686 Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.
695 Triggers can return one of the following:
699 <term><literal>return;</literal></term>
702 Execute the statement
708 <term><literal>"SKIP"</literal></term>
711 Don't execute the statement
717 <term><literal>"MODIFY"</literal></term>
720 Indicates that the <literal>NEW</literal> row was modified by
729 Here is an example of a trigger function, illustrating some of the
737 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
738 if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
739 return "SKIP"; # skip INSERT/UPDATE command
740 } elsif ($_TD->{new}{v} ne "immortal") {
741 $_TD->{new}{v} .= "(modified by trigger)";
742 return "MODIFY"; # modify row and execute INSERT/UPDATE command
744 return; # execute INSERT/UPDATE command
748 CREATE TRIGGER test_valid_id_trig
749 BEFORE INSERT OR UPDATE ON test
750 FOR EACH ROW EXECUTE PROCEDURE valid_id();
755 <sect1 id="plperl-missing">
756 <title>Limitations and Missing Features</title>
759 The following features are currently missing from PL/Perl, but they
760 would make welcome contributions.
765 PL/Perl functions cannot call each other directly (because they
766 are anonymous subroutines inside Perl).
772 SPI is not yet fully implemented.
778 If you are fetching very large data sets using
779 <literal>spi_exec_query</literal>, you should be aware that
780 these will all go into memory. You can avoid this by using
781 <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
785 A similar problem occurs if a set-returning function passes a
786 large set of rows back to postgres via <literal>return</literal>. You
787 can avoid this problem too by instead using
788 <literal>return_next</literal> for each row returned, as shown
799 <!-- Keep this comment at the end of the file
804 sgml-minimize-attributes:nil
805 sgml-always-quote-attributes:t
808 sgml-parent-document:nil
809 sgml-default-dtd-file:"./reference.ced"
810 sgml-exposed-tags:nil
811 sgml-local-catalogs:("/usr/lib/sgml/catalog")
812 sgml-local-ecat-files:nil