2 $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.41 2005/06/05 03:16:29 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
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">
51 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
52 # PL/Perl function body
55 The body of the function is ordinary Perl code.
59 The syntax of the <command>CREATE FUNCTION</command> command requires
60 the function body to be written as a string constant. It is usually
61 most convenient to use dollar quoting (see <xref
62 linkend="sql-syntax-dollar-quoting">) for the string constant.
63 If you choose to use regular single-quoted string constant syntax,
64 you must escape single quote marks (<literal>'</>) and backslashes
65 (<literal>\</>) used in the body of the function, typically by
66 doubling them (see <xref linkend="sql-syntax-strings">).
70 Arguments and results are handled as in any other Perl subroutine:
71 arguments are passed in <varname>@_</varname>, and a result value
72 is returned with <literal>return</> or as the last expression
73 evaluated in the function.
77 For example, a function returning the greater of two integer values
81 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
82 if ($_[0] > $_[1]) { return $_[0]; }
89 If an SQL null value<indexterm><primary>null value</><secondary
90 sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
91 the argument value will appear as <quote>undefined</> in Perl. The
92 above function definition will not behave very nicely with null
93 inputs (in fact, it will act as though they are zeroes). We could
94 add <literal>STRICT</> to the function definition to make
95 <productname>PostgreSQL</productname> do something more reasonable:
96 if a null value is passed, the function will not be called at all,
97 but will just return a null result automatically. Alternatively,
98 we could check for undefined inputs in the function body. For
99 example, suppose that we wanted <function>perl_max</function> with
100 one null and one nonnull argument to return the nonnull argument,
101 rather than a null value:
104 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
107 if (! defined $b) { return undef; }
110 if (! defined $b) { return $a; }
111 if ($a > $b) { return $a; }
115 As shown above, to return an SQL null value from a PL/Perl
116 function, return an undefined value. This can be done whether the
117 function is strict or not.
121 Composite-type arguments are passed to the function as references
122 to hashes. The keys of the hash are the attribute names of the
123 composite type. Here is an example:
126 CREATE TABLE employee (
132 CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
134 return $emp->{basesalary} + $emp->{bonus};
137 SELECT name, empcomp(employee.*) FROM employee;
142 A PL/Perl function can return a composite-type result using the same
143 approach: return a reference to a hash that has the required attributes.
147 CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
149 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
150 return {f2 => 'hello', f1 => 1, f3 => 'world'};
153 SELECT * FROM perl_row();
156 Any columns in the declared result data type that are not present in the
157 hash will be returned as NULLs.
161 PL/Perl functions can also return sets of either scalar or composite
162 types. To do this, return a reference to an array that contains
163 either scalars or references to hashes, respectively. Here are
164 some simple examples:
167 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
171 SELECT * FROM perl_set_int(5);
174 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
176 { f1 => 1, f2 => 'Hello', f3 => 'World' },
177 { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
178 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
182 SELECT * FROM perl_set();
185 When you do this, Perl will have to build the entire array in memory;
186 therefore the technique does not scale to very large result sets. You
187 can instead call <function>return_next</function> for each element of
188 the result set, passing it either a scalar or a reference to a hash,
189 as appropriate to your function's return type.
193 <application>PL/Perl</> does not currently have full support for
194 domain types: it treats a domain the same as the underlying scalar
195 type. This means that constraints associated with the domain will
196 not be enforced. This is not an issue for function arguments, but
197 it is a hazard if you declare a <application>PL/Perl</> function
198 as returning a domain type.
202 <sect1 id="plperl-database">
203 <title>Database Access from PL/Perl</title>
206 Access to the database itself from your Perl function can be done
207 via the function <function>spi_exec_query</function> described
208 below, or via an experimental module
209 <ulink url="http://www.cpan.org/modules/by-module/DBD/APILOS/">
210 <literal>DBD::PgSPI</literal></ulink>
211 (also available at <ulink url="http://www.cpan.org/SITES.html">
212 <acronym>CPAN mirror sites</></ulink>). This module makes available a
213 <acronym>DBI</>-compliant database-handle named
214 <varname>$pg_dbh</varname> that can be used to perform queries with
215 normal <acronym>DBI</>
216 syntax.<indexterm><primary>DBI</></indexterm>
220 PL/Perl itself presently provides two additional Perl commands:
225 <primary>spi_exec_query</primary>
226 <secondary>in PL/Perl</secondary>
229 <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
230 <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
233 Executes an SQL command. Here is an example of a query
234 (<command>SELECT</command> command) with the optional maximum
237 $rv = spi_exec_query('SELECT * FROM my_table', 5);
239 This returns up to 5 rows from the table
240 <literal>my_table</literal>. If <literal>my_table</literal>
241 has a column <literal>my_column</literal>, you can get that
242 value from row <literal>$i</literal> of the result like this:
244 $foo = $rv->{rows}[$i]->{my_column};
246 The total number of rows returned from a <command>SELECT</command>
247 query can be accessed like this:
249 $nrows = $rv->{processed}
254 Here is an example using a different command type:
256 $query = "INSERT INTO my_table VALUES (1, 'test')";
257 $rv = spi_exec_query($query);
259 You can then access the command status (e.g.,
260 <literal>SPI_OK_INSERT</literal>) like this:
262 $res = $rv->{status};
264 To get the number of rows affected, do:
266 $nrows = $rv->{processed};
271 Here is a complete example:
278 INSERT INTO test (i, v) VALUES (1, 'first line');
279 INSERT INTO test (i, v) VALUES (2, 'second line');
280 INSERT INTO test (i, v) VALUES (3, 'third line');
281 INSERT INTO test (i, v) VALUES (4, 'immortal');
283 CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
285 my $rv = spi_exec_query('select i, v from test;');
286 my $status = $rv->{status};
287 my $nrows = $rv->{processed};
288 foreach my $rn (0 .. $nrows - 1) {
289 my $row = $rv->{rows}[$rn];
290 $row->{i} += 200 if defined($row->{i});
291 $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
297 SELECT * FROM test_munge();
305 <primary>elog</primary>
306 <secondary>in PL/Perl</secondary>
309 <term><literal><function>elog</>(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</literal></term>
312 Emit a log or error message. Possible levels are
313 <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
314 <literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
316 raises an error condition; if this is not trapped by the surrounding
317 Perl code, the error propagates out to the calling query, causing
318 the current transaction or subtransaction to be aborted. This
319 is effectively the same as the Perl <literal>die</> command.
320 The other levels only generate messages of different
322 Whether messages of a particular priority are reported to the client,
323 written to the server log, or both is controlled by the
324 <xref linkend="guc-log-min-messages"> and
325 <xref linkend="guc-client-min-messages"> configuration
326 variables. See <xref linkend="runtime-config"> for more
335 <sect1 id="plperl-data">
336 <title>Data Values in PL/Perl</title>
339 The argument values supplied to a PL/Perl function's code are
340 simply the input arguments converted to text form (just as if they
341 had been displayed by a <command>SELECT</command> statement).
342 Conversely, the <literal>return</> command will accept any string
343 that is acceptable input format for the function's declared return
344 type. So, within the PL/Perl function,
345 all values are just text strings.
349 <sect1 id="plperl-global">
350 <title>Global Values in PL/Perl</title>
353 You can use the global hash <varname>%_SHARED</varname> to store
354 data, including code references, between function calls for the
355 lifetime of the current session.
359 Here is a simple example for shared data:
361 CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
362 if ($_SHARED{$_[0]} = $_[1]) {
365 return "can't set shared variable $_[0] to $_[1]";
369 CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
370 return $_SHARED{$_[0]};
373 SELECT set_var('sample', 'Hello, PL/Perl! How's tricks?');
374 SELECT get_var('sample');
379 Here is a slightly more complicated example using a code reference:
382 CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
383 $_SHARED{myquote} = sub {
385 $arg =~ s/(['\\])/\\$1/g;
390 SELECT myfuncs(); /* initializes the function */
392 /* Set up a function that uses the quote function */
394 CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
395 my $text_to_quote = shift;
396 my $qfunc = $_SHARED{myquote};
397 return &$qfunc($text_to_quote);
401 (You could have replaced the above with the one-liner
402 <literal>return $_SHARED{myquote}->($_[0]);</literal>
403 at the expense of readability.)
407 <sect1 id="plperl-trusted">
408 <title>Trusted and Untrusted PL/Perl</title>
410 <indexterm zone="plperl-trusted">
411 <primary>trusted</primary>
412 <secondary>PL/Perl</secondary>
416 Normally, PL/Perl is installed as a <quote>trusted</> programming
417 language named <literal>plperl</>. In this setup, certain Perl
418 operations are disabled to preserve security. In general, the
419 operations that are restricted are those that interact with the
420 environment. This includes file handle operations,
421 <literal>require</literal>, and <literal>use</literal> (for
422 external modules). There is no way to access internals of the
423 database server process or to gain OS-level access with the
424 permissions of the server process,
425 as a C function can do. Thus, any unprivileged database user may
426 be permitted to use this language.
430 Here is an example of a function that will not work because file
431 system operations are not allowed for security reasons:
433 CREATE FUNCTION badfunc() RETURNS integer AS $$
434 open(TEMP, ">/tmp/badfile");
435 print TEMP "Gotcha!\n";
439 The creation of the function will succeed, but executing it will not.
443 Sometimes it is desirable to write Perl functions that are not
444 restricted. For example, one might want a Perl function that sends
445 mail. To handle these cases, PL/Perl can also be installed as an
446 <quote>untrusted</> language (usually called
447 <application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
448 In this case the full Perl language is available. If the
449 <command>createlang</command> program is used to install the
450 language, the language name <literal>plperlu</literal> will select
451 the untrusted PL/Perl variant.
455 The writer of a <application>PL/PerlU</> function must take care that the function
456 cannot be used to do anything unwanted, since it will be able to do
457 anything that could be done by a user logged in as the database
458 administrator. Note that the database system allows only database
459 superusers to create functions in untrusted languages.
463 If the above function was created by a superuser using the language
464 <literal>plperlu</>, execution would succeed.
468 <sect1 id="plperl-triggers">
469 <title>PL/Perl Triggers</title>
472 PL/Perl can be used to write trigger functions. In a trigger function,
473 the hash reference <varname>$_TD</varname> contains information about the
474 current trigger event. The fields of the <varname>$_TD</varname> hash
479 <term><literal>$_TD->{new}{foo}</literal></term>
482 <literal>NEW</literal> value of column <literal>foo</literal>
488 <term><literal>$_TD->{old}{foo}</literal></term>
491 <literal>OLD</literal> value of column <literal>foo</literal>
497 <term><literal>$_TD->{name}</literal></term>
500 Name of the trigger being called
506 <term><literal>$_TD->{event}</literal></term>
509 Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
515 <term><literal>$_TD->{when}</literal></term>
518 When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
524 <term><literal>$_TD->{level}</literal></term>
527 The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
533 <term><literal>$_TD->{relid}</literal></term>
536 OID of the table on which the trigger fired
542 <term><literal>$_TD->{relname}</literal></term>
545 Name of the table on which the trigger fired
551 <term><literal>$_TD->{argc}</literal></term>
554 Number of arguments of the trigger function
560 <term><literal>@{$_TD->{args}}</literal></term>
563 Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.
572 Triggers can return one of the following:
576 <term><literal>return;</literal></term>
579 Execute the statement
585 <term><literal>"SKIP"</literal></term>
588 Don't execute the statement
594 <term><literal>"MODIFY"</literal></term>
597 Indicates that the <literal>NEW</literal> row was modified by
606 Here is an example of a trigger function, illustrating some of the
614 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
615 if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
616 return "SKIP"; # skip INSERT/UPDATE command
617 } elsif ($_TD->{new}{v} ne "immortal") {
618 $_TD->{new}{v} .= "(modified by trigger)";
619 return "MODIFY"; # modify row and execute INSERT/UPDATE command
621 return; # execute INSERT/UPDATE command
625 CREATE TRIGGER test_valid_id_trig
626 BEFORE INSERT OR UPDATE ON test
627 FOR EACH ROW EXECUTE PROCEDURE valid_id();
632 <sect1 id="plperl-missing">
633 <title>Limitations and Missing Features</title>
636 The following features are currently missing from PL/Perl, but they
637 would make welcome contributions.
642 PL/Perl functions cannot call each other directly (because they
643 are anonymous subroutines inside Perl).
649 SPI is not yet fully implemented.
655 In the current implementation, if you are fetching or returning
656 very large data sets, you should be aware that these will all go
666 <!-- Keep this comment at the end of the file
671 sgml-minimize-attributes:nil
672 sgml-always-quote-attributes:t
675 sgml-parent-document:nil
676 sgml-default-dtd-file:"./reference.ced"
677 sgml-exposed-tags:nil
678 sgml-local-catalogs:("/usr/lib/sgml/catalog")
679 sgml-local-ecat-files:nil