granicus.if.org Git - postgresql/blob - doc/src/sgml/plperl.sgml

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.59 2006/11/13 17:13:56 adunstan Exp $ -->
   2
   3  <chapter id="plperl">
   4   <title>PL/Perl - Perl Procedural Language</title>
   5
   6   <indexterm zone="plperl">
   7    <primary>PL/Perl</primary>
   8   </indexterm>
   9
  10   <indexterm zone="plperl">
  11    <primary>Perl</primary>
  12   </indexterm>
  13
  14   <para>
  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.com">Perl programming language</ulink>.
  18   </para>
  19
  20   <para> The usual advantage to using PL/Perl is that this allows use,
  21    within stored functions, of the manyfold <quote>string
  22     munging</quote> operators and functions available for Perl.  Parsing
  23    complex strings may be be easier using Perl than it is with the
  24    string functions and control structures provided in PL/pgSQL.</para>
  25
  26   <para>
  27    To install PL/Perl in a particular database, use
  28    <literal>createlang plperl <replaceable>dbname</></literal>.
  29   </para>
  30
  31   <tip>
  32    <para>
  33     If a language is installed into <literal>template1</>, all subsequently
  34     created databases will have the language installed automatically.
  35    </para>
  36   </tip>
  37
  38   <note>
  39    <para>
  40     Users of source packages must specially enable the build of
  41     PL/Perl during the installation process.  (Refer to <xref
  42     linkend="install-short"> for more information.)  Users of
  43     binary packages might find PL/Perl in a separate subpackage.
  44    </para>
  45   </note>
  46
  47  <sect1 id="plperl-funcs">
  48   <title>PL/Perl Functions and Arguments</title>
  49
  50   <para>
  51    To create a function in the PL/Perl language, use the standard
  52    <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
  53    syntax:
  54
  55 <programlisting>
  56 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
  57     # PL/Perl function body
  58 $$ LANGUAGE plperl;
  59 </programlisting>
  60    The body of the function is ordinary Perl code. In fact, the PL/Perl
  61    glue code wraps it inside a Perl subroutine. A PL/Perl function must
  62    always return a scalar value.  You can return more complex structures
  63    (arrays, records, and sets) by returning a reference, as discussed below.
  64    Never return a list.
  65   </para>
  66
  67   <note>
  68    <para>
  69     The use of named nested subroutines is dangerous in Perl, especially if
  70     they refer to lexical variables in the enclosing scope. Because a PL/Perl
  71     function is wrapped in a subroutine, any named subroutine you create will
  72     be nested. In general, it is far safer to create anonymous subroutines
  73     which you call via a coderef. See the <literal>perldiag</literal>
  74     man page for more details.
  75    </para>
  76   </note>
  77
  78   <para>
  79    The syntax of the <command>CREATE FUNCTION</command> command requires
  80    the function body to be written as a string constant.  It is usually
  81    most convenient to use dollar quoting (see <xref
  82    linkend="sql-syntax-dollar-quoting">) for the string constant.
  83    If you choose to use regular single-quoted string constant syntax,
  84    you must escape single quote marks (<literal>'</>) and backslashes
  85    (<literal>\</>) used in the body of the function, typically by
  86    doubling them (see <xref linkend="sql-syntax-strings">).
  87   </para>
  88
  89   <para>
  90    Arguments and results are handled as in any other Perl subroutine:
  91    arguments are passed in <varname>@_</varname>, and a result value
  92    is returned with <literal>return</> or as the last expression
  93    evaluated in the function.
  94   </para>
  95
  96   <para>
  97    For example, a function returning the greater of two integer values
  98    could be defined as:
  99
 100 <programlisting>
 101 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
 102     if ($_[0] &gt; $_[1]) { return $_[0]; }
 103     return $_[1];
 104 $$ LANGUAGE plperl;
 105 </programlisting>
 106   </para>
 107
 108   <para>
 109    If an SQL null value<indexterm><primary>null value</><secondary
 110    sortas="PL/Perl">in PL/Perl</></indexterm> is passed to a function,
 111    the argument value will appear as <quote>undefined</> in Perl.  The
 112    above function definition will not behave very nicely with null
 113    inputs (in fact, it will act as though they are zeroes).  We could
 114    add <literal>STRICT</> to the function definition to make
 115    <productname>PostgreSQL</productname> do something more reasonable:
 116    if a null value is passed, the function will not be called at all,
 117    but will just return a null result automatically.  Alternatively,
 118    we could check for undefined inputs in the function body.  For
 119    example, suppose that we wanted <function>perl_max</function> with
 120    one null and one nonnull argument to return the nonnull argument,
 121    rather than a null value:
 122
 123 <programlisting>
 124 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
 125     my ($x,$y) = @_;
 126     if (! defined $x) {
 127         if (! defined $y) { return undef; }
 128         return $y;
 129     }
 130     if (! defined $y) { return $x; }
 131     if ($x &gt; $y) { return $x; }
 132     return $y;
 133 $$ LANGUAGE plperl;
 134 </programlisting>
 135    As shown above, to return an SQL null value from a PL/Perl
 136    function, return an undefined value.  This can be done whether the
 137    function is strict or not.
 138   </para>
 139
 140   <para>
 141    Perl can return <productname>PostgreSQL</productname> arrays as
 142    references to Perl arrays.  Here is an example:
 143
 144 <programlisting>
 145 CREATE OR REPLACE function returns_array()
 146 RETURNS text[][] AS $$
 147     return [['a"b','c,d'],['e\\f','g']];
 148 $$ LANGUAGE plperl;
 149
 150 select returns_array();
 151 </programlisting>
 152   </para>
 153
 154   <para>
 155    Composite-type arguments are passed to the function as references
 156    to hashes.  The keys of the hash are the attribute names of the
 157    composite type.  Here is an example:
 158
 159 <programlisting>
 160 CREATE TABLE employee (
 161     name text,
 162     basesalary integer,
 163     bonus integer
 164 );
 165
 166 CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
 167     my ($emp) = @_;
 168     return $emp-&gt;{basesalary} + $emp-&gt;{bonus};
 169 $$ LANGUAGE plperl;
 170
 171 SELECT name, empcomp(employee.*) FROM employee;
 172 </programlisting>
 173   </para>
 174
 175   <para>
 176    A PL/Perl function can return a composite-type result using the same
 177    approach: return a reference to a hash that has the required attributes.
 178    For example,
 179
 180 <programlisting>
 181 CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
 182
 183 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
 184     return {f2 =&gt; 'hello', f1 =&gt; 1, f3 =&gt; 'world'};
 185 $$ LANGUAGE plperl;
 186
 187 SELECT * FROM perl_row();
 188 </programlisting>
 189
 190    Any columns in the declared result data type that are not present in the
 191    hash will be returned as null values.
 192   </para>
 193
 194   <para>
 195     PL/Perl functions can also return sets of either scalar or
 196     composite types.  Usually you'll want to return rows one at a
 197     time, both to speed up startup time and to keep from queueing up
 198     the entire result set in memory.  You can do this with
 199     <function>return_next</function> as illustrated below.  Note that
 200     after the last <function>return_next</function>, you must put
 201     either <literal>return</literal> or (better) <literal>return
 202     undef</literal>.
 203
 204 <programlisting>
 205 CREATE OR REPLACE FUNCTION perl_set_int(int)
 206 RETURNS SETOF INTEGER AS $$
 207     foreach (0..$_[0]) {
 208         return_next($_);
 209     }
 210     return undef;
 211 $$ LANGUAGE plperl;
 212
 213 SELECT * FROM perl_set_int(5);
 214
 215 CREATE OR REPLACE FUNCTION perl_set()
 216 RETURNS SETOF testrowperl AS $$
 217     return_next({ f1 =&gt; 1, f2 =&gt; 'Hello', f3 =&gt; 'World' });
 218     return_next({ f1 =&gt; 2, f2 =&gt; 'Hello', f3 =&gt; 'PostgreSQL' });
 219     return_next({ f1 =&gt; 3, f2 =&gt; 'Hello', f3 =&gt; 'PL/Perl' });
 220     return undef;
 221 $$ LANGUAGE plperl;
 222 </programlisting>
 223
 224     For small result sets, you can return a reference to an array that
 225     contains either scalars, references to arrays, or references to
 226     hashes for simple types, array types, and composite types,
 227     respectively.  Here are some simple examples of returning the entire
 228     result set as an array reference:
 229
 230 <programlisting>
 231 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
 232     return [0..$_[0]];
 233 $$ LANGUAGE plperl;
 234
 235 SELECT * FROM perl_set_int(5);
 236
 237 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
 238     return [
 239         { f1 =&gt; 1, f2 =&gt; 'Hello', f3 =&gt; 'World' },
 240         { f1 =&gt; 2, f2 =&gt; 'Hello', f3 =&gt; 'PostgreSQL' },
 241         { f1 =&gt; 3, f2 =&gt; 'Hello', f3 =&gt; 'PL/Perl' }
 242     ];
 243 $$ LANGUAGE plperl;
 244
 245 SELECT * FROM perl_set();
 246 </programlisting>
 247   </para>
 248
 249   <para>
 250    If you wish to use the <literal>strict</> pragma with your code,
 251    the easiest way to do so is to <command>SET</>
 252    <literal>plperl.use_strict</literal> to true.  This parameter affects
 253    subsequent compilations of <application>PL/Perl</> functions, but not
 254    functions already compiled in the current session.  To set the
 255    parameter before <application>PL/Perl</> has been loaded, it is
 256    necessary to have added <quote><literal>plperl</></> to the <xref
 257    linkend="guc-custom-variable-classes"> list in
 258    <filename>postgresql.conf</filename>.
 259   </para>
 260
 261   <para>
 262    Another way to use the <literal>strict</> pragma is to put
 263 <programlisting>
 264 use strict;
 265 </programlisting>
 266    in the function body.  But this only works in <application>PL/PerlU</>
 267    functions, since <literal>use</> is not a trusted operation.  In
 268    <application>PL/Perl</> functions you can instead do
 269 <programlisting>
 270 BEGIN { strict->import(); }
 271 </programlisting>
 272   </para>
 273  </sect1>
 274
 275  <sect1 id="plperl-database">
 276   <title>Database Access from PL/Perl</title>
 277
 278   <para>
 279    Access to the database itself from your Perl function can be done
 280    via the function <function>spi_exec_query</function> described
 281    below, or via an experimental module
 282    <ulink url="http://www.cpan.org/modules/by-module/DBD/APILOS/">
 283    <literal>DBD::PgSPI</literal></ulink>
 284    (also available at <ulink url="http://www.cpan.org/SITES.html">
 285    <acronym>CPAN mirror sites</></ulink>).  This module makes available a
 286    <acronym>DBI</>-compliant database-handle named
 287    <varname>$pg_dbh</varname> that can be used to perform queries with
 288    normal <acronym>DBI</>
 289    syntax.<indexterm><primary>DBI</></indexterm>
 290   </para>
 291
 292   <para>
 293    PL/Perl provides additional Perl commands:
 294
 295    <variablelist>
 296     <varlistentry>
 297      <indexterm>
 298       <primary>spi_exec_query</primary>
 299       <secondary>in PL/Perl</secondary>
 300      </indexterm>
 301
 302      <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
 303      <term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term>
 304      <term><literal><function>spi_fetchrow</>(<replaceable>cursor</replaceable>)</literal></term>
 305      <term><literal><function>spi_prepare</>(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</literal></term>
 306      <term><literal><function>spi_exec_prepared</>(<replaceable>plan</replaceable>)</literal></term>
 307      <term><literal><function>spi_query_prepared</>(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</literal></term>
 308      <term><literal><function>spi_cursor_close</>(<replaceable>cursor</replaceable>)</literal></term>
 309      <term><literal><function>spi_freeplan</>(<replaceable>plan</replaceable>)</literal></term>
 310
 311      <listitem>
 312       <para>
 313        <literal>spi_exec_query</literal> executes an SQL command and
 314 returns the entire row set as a reference to an array of hash
 315 references.  <emphasis>You should only use this command when you know
 316 that the result set will be relatively small.</emphasis>  Here is an
 317 example of a query (<command>SELECT</command> command) with the
 318 optional maximum number of rows:
 319
 320 <programlisting>
 321 $rv = spi_exec_query('SELECT * FROM my_table', 5);
 322 </programlisting>
 323         This returns up to 5 rows from the table
 324         <literal>my_table</literal>.  If <literal>my_table</literal>
 325         has a column <literal>my_column</literal>, you can get that
 326         value from row <literal>$i</literal> of the result like this:
 327 <programlisting>
 328 $foo = $rv-&gt;{rows}[$i]-&gt;{my_column};
 329 </programlisting>
 330        The total number of rows returned from a <command>SELECT</command>
 331        query can be accessed like this:
 332 <programlisting>
 333 $nrows = $rv-&gt;{processed}
 334 </programlisting>
 335       </para>
 336
 337       <para>
 338        Here is an example using a different command type:
 339 <programlisting>
 340 $query = "INSERT INTO my_table VALUES (1, 'test')";
 341 $rv = spi_exec_query($query);
 342 </programlisting>
 343        You can then access the command status (e.g.,
 344        <literal>SPI_OK_INSERT</literal>) like this:
 345 <programlisting>
 346 $res = $rv-&gt;{status};
 347 </programlisting>
 348        To get the number of rows affected, do:
 349 <programlisting>
 350 $nrows = $rv-&gt;{processed};
 351 </programlisting>
 352       </para>
 353
 354       <para>
 355        Here is a complete example:
 356 <programlisting>
 357 CREATE TABLE test (
 358     i int,
 359     v varchar
 360 );
 361
 362 INSERT INTO test (i, v) VALUES (1, 'first line');
 363 INSERT INTO test (i, v) VALUES (2, 'second line');
 364 INSERT INTO test (i, v) VALUES (3, 'third line');
 365 INSERT INTO test (i, v) VALUES (4, 'immortal');
 366
 367 CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
 368     my $rv = spi_exec_query('select i, v from test;');
 369     my $status = $rv-&gt;{status};
 370     my $nrows = $rv-&gt;{processed};
 371     foreach my $rn (0 .. $nrows - 1) {
 372         my $row = $rv-&gt;{rows}[$rn];
 373         $row-&gt;{i} += 200 if defined($row-&gt;{i});
 374         $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
 375         return_next($row);
 376     }
 377     return undef;
 378 $$ LANGUAGE plperl;
 379
 380 SELECT * FROM test_munge();
 381 </programlisting>
 382     </para>
 383     <para>
 384     <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
 385     work together as a pair for row sets which may be large, or for cases
 386     where you wish to return rows as they arrive.
 387     <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
 388     <literal>spi_query</literal>. The following example illustrates how
 389     you use them together:
 390
 391 <programlisting>
 392 CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
 393
 394 CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
 395     use Digest::MD5 qw(md5_hex);
 396     my $file = '/usr/share/dict/words';
 397     my $t = localtime;
 398     elog(NOTICE, "opening file $file at $t" );
 399     open my $fh, '&lt;', $file # ooh, it's a file access!
 400         or elog(ERROR, "can't open $file for reading: $!");
 401     my @words = &lt;$fh&gt;;
 402     close $fh;
 403     $t = localtime;
 404     elog(NOTICE, "closed file $file at $t");
 405     chomp(@words);
 406     my $row;
 407     my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
 408     while (defined ($row = spi_fetchrow($sth))) {
 409         return_next({
 410             the_num =&gt; $row-&gt;{a},
 411             the_text =&gt; md5_hex($words[rand @words])
 412         });
 413     }
 414     return;
 415 $$ LANGUAGE plperlu;
 416
 417 SELECT * from lotsa_md5(500);
 418 </programlisting>
 419     </para>
 420
 421     <para>
 422     <literal>spi_prepare</literal>, <literal>spi_query_prepared</literal>, <literal>spi_exec_prepared</literal>,
 423     and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries. Once
 424     a query plan is prepared by a call to <literal>spi_prepare</literal>, the plan can be used instead
 425     of the string query, either in <literal>spi_exec_prepared</literal>, where the result is the same as returned
 426     by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepared</literal> which returns a cursor
 427     exactly as <literal>spi_query</literal> does, which can be later passed to <literal>spi_fetchrow</literal>.
 428     </para>
 429
 430     <para>
 431     The advantage of prepared queries is that is it possible to use one prepared plan for more
 432     than one query execution. After the plan is not needed anymore, it may be freed with
 433     <literal>spi_freeplan</literal>:
 434     </para>
 435
 436     <para>
 437     <programlisting>
 438 CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
 439         $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
 440 $$ LANGUAGE plperl;
 441
 442 CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
 443         return spi_exec_prepared(
 444                 $_SHARED{my_plan},
 445                 $_[0],
 446         )->{rows}->[0]->{now};
 447 $$ LANGUAGE plperl;
 448
 449 CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
 450         spi_freeplan( $_SHARED{my_plan});
 451         undef $_SHARED{my_plan};
 452 $$ LANGUAGE plperl;
 453
 454 SELECT init();
 455 SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
 456 SELECT done();
 457
 458   add_time  |  add_time  |  add_time
 459 ------------+------------+------------
 460  2005-12-10 | 2005-12-11 | 2005-12-12
 461     </programlisting>
 462     </para>
 463
 464     <para>
 465     Note that the parameter subscript in <literal>spi_prepare</literal> is defined via
 466     $1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily
 467     lead to hard-to-catch bugs.
 468     </para>
 469
 470     <para>
 471      Normally, <function>spi_fetchrow</> should be repeated until it
 472      returns <literal>undef</literal>, indicating that there are no more
 473      rows to read.  The cursor is automatically freed when
 474      <function>spi_fetchrow</> returns <literal>undef</literal>.
 475      If you do not wish to read all the rows, instead call
 476      <function>spi_cursor_close</> to free the cursor.
 477      Failure to do so will result in memory leaks.
 478     </para>
 479      </listitem>
 480     </varlistentry>
 481
 482     <varlistentry>
 483      <indexterm>
 484       <primary>elog</primary>
 485       <secondary>in PL/Perl</secondary>
 486      </indexterm>
 487
 488      <term><literal><function>elog</>(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</literal></term>
 489      <listitem>
 490       <para>
 491        Emit a log or error message. Possible levels are
 492        <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
 493        <literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
 494        <literal>ERROR</>
 495         raises an error condition; if this is not trapped by the surrounding
 496         Perl code, the error propagates out to the calling query, causing
 497         the current transaction or subtransaction to be aborted.  This
 498         is effectively the same as the Perl <literal>die</> command.
 499         The other levels only generate messages of different
 500         priority levels.
 501         Whether messages of a particular priority are reported to the client,
 502         written to the server log, or both is controlled by the
 503         <xref linkend="guc-log-min-messages"> and
 504         <xref linkend="guc-client-min-messages"> configuration
 505         variables. See <xref linkend="runtime-config"> for more
 506         information.
 507       </para>
 508      </listitem>
 509     </varlistentry>
 510    </variablelist>
 511   </para>
 512  </sect1>
 513
 514  <sect1 id="plperl-data">
 515   <title>Data Values in PL/Perl</title>
 516
 517   <para>
 518    The argument values supplied to a PL/Perl function's code are
 519    simply the input arguments converted to text form (just as if they
 520    had been displayed by a <command>SELECT</command> statement).
 521    Conversely, the <literal>return</> command will accept any string
 522    that is acceptable input format for the function's declared return
 523    type.  So, within the PL/Perl function,
 524    all values are just text strings.
 525   </para>
 526  </sect1>
 527
 528  <sect1 id="plperl-global">
 529   <title>Global Values in PL/Perl</title>
 530
 531   <para>
 532     You can use the global hash <varname>%_SHARED</varname> to store
 533     data, including code references, between function calls for the
 534     lifetime of the current session.
 535   </para>
 536
 537   <para>
 538     Here is a simple example for shared data:
 539 <programlisting>
 540 CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
 541     if ($_SHARED{$_[0]} = $_[1]) {
 542         return 'ok';
 543     } else {
 544         return "can't set shared variable $_[0] to $_[1]";
 545     }
 546 $$ LANGUAGE plperl;
 547
 548 CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
 549     return $_SHARED{$_[0]};
 550 $$ LANGUAGE plperl;
 551
 552 SELECT set_var('sample', 'Hello, PL/Perl!  How's tricks?');
 553 SELECT get_var('sample');
 554 </programlisting>
 555   </para>
 556
 557   <para>
 558    Here is a slightly more complicated example using a code reference:
 559
 560 <programlisting>
 561 CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
 562     $_SHARED{myquote} = sub {
 563         my $arg = shift;
 564         $arg =~ s/(['\\])/\\$1/g;
 565         return "'$arg'";
 566     };
 567 $$ LANGUAGE plperl;
 568
 569 SELECT myfuncs(); /* initializes the function */
 570
 571 /* Set up a function that uses the quote function */
 572
 573 CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
 574     my $text_to_quote = shift;
 575     my $qfunc = $_SHARED{myquote};
 576     return &amp;$qfunc($text_to_quote);
 577 $$ LANGUAGE plperl;
 578 </programlisting>
 579
 580    (You could have replaced the above with the one-liner
 581    <literal>return $_SHARED{myquote}-&gt;($_[0]);</literal>
 582    at the expense of readability.)
 583   </para>
 584  </sect1>
 585
 586  <sect1 id="plperl-trusted">
 587   <title>Trusted and Untrusted PL/Perl</title>
 588
 589   <indexterm zone="plperl-trusted">
 590    <primary>trusted</primary>
 591    <secondary>PL/Perl</secondary>
 592   </indexterm>
 593
 594   <para>
 595    Normally, PL/Perl is installed as a <quote>trusted</> programming
 596    language named <literal>plperl</>.  In this setup, certain Perl
 597    operations are disabled to preserve security.  In general, the
 598    operations that are restricted are those that interact with the
 599    environment. This includes file handle operations,
 600    <literal>require</literal>, and <literal>use</literal> (for
 601    external modules).  There is no way to access internals of the
 602    database server process or to gain OS-level access with the
 603    permissions of the server process,
 604    as a C function can do.  Thus, any unprivileged database user may
 605    be permitted to use this language.
 606   </para>
 607
 608   <para>
 609    Here is an example of a function that will not work because file
 610    system operations are not allowed for security reasons:
 611 <programlisting>
 612 CREATE FUNCTION badfunc() RETURNS integer AS $$
 613     my $tmpfile = "/tmp/badfile";
 614     open my $fh, '&gt;', $tmpfile
 615         or elog(ERROR, qq{could not open the file "$tmpfile": $!});
 616     print $fh "Testing writing to a file\n";
 617     close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
 618     return 1;
 619 $$ LANGUAGE plperl;
 620 </programlisting>
 621     The creation of this function will fail as its use of a forbidden
 622     operation will be be caught by the validator.
 623   </para>
 624
 625   <para>
 626    Sometimes it is desirable to write Perl functions that are not
 627    restricted.  For example, one might want a Perl function that sends
 628    mail.  To handle these cases, PL/Perl can also be installed as an
 629    <quote>untrusted</> language (usually called
 630    <application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
 631    In this case the full Perl language is available.  If the
 632    <command>createlang</command> program is used to install the
 633    language, the language name <literal>plperlu</literal> will select
 634    the untrusted PL/Perl variant.
 635   </para>
 636
 637   <para>
 638    The writer of a <application>PL/PerlU</> function must take care that the function
 639    cannot be used to do anything unwanted, since it will be able to do
 640    anything that could be done by a user logged in as the database
 641    administrator.  Note that the database system allows only database
 642    superusers to create functions in untrusted languages.
 643   </para>
 644
 645   <para>
 646    If the above function was created by a superuser using the language
 647    <literal>plperlu</>, execution would succeed.
 648   </para>
 649
 650   <note>
 651     <para>
 652           For security reasons, to stop a leak of privileged operations from
 653       <application>PL/PerlU</> to <application>PL/Perl</>, these two languages
 654           have to run in separate instances of the Perl interpreter. If your
 655           Perl installation has been appropriately compiled, this is not a problem.
 656           However, not all installations are compiled with the requisite flags.
 657           If <productname>PostgreSQL</> detects that this is the case then it will
 658           not start a second interpreter, but instead create an error. In
 659           consequence, in such an installation, you cannot use both
 660           <application>PL/PerlU</> and <application>PL/Perl</> in the same backend
 661           process. The remedy for this is to obtain a Perl installation created
 662           with the appropriate flags, namely either <literal>usemultiplicity</> or
 663           both <literal>usethreads</> and <literal>useithreads</>.
 664           For more details,see the <literal>perlembed</> manual page.
 665     </para>
 666   </note>
 667
 668  </sect1>
 669
 670  <sect1 id="plperl-triggers">
 671   <title>PL/Perl Triggers</title>
 672
 673   <para>
 674    PL/Perl can be used to write trigger functions.  In a trigger function,
 675    the hash reference <varname>$_TD</varname> contains information about the
 676    current trigger event. <varname>$_TD</> is a global variable,
 677    which gets a separate local value for each invocation of the trigger.
 678    The fields of the <varname>$_TD</varname> hash reference are:
 679
 680    <variablelist>
 681     <varlistentry>
 682      <term><literal>$_TD-&gt;{new}{foo}</literal></term>
 683      <listitem>
 684       <para>
 685        <literal>NEW</literal> value of column <literal>foo</literal>
 686       </para>
 687      </listitem>
 688     </varlistentry>
 689
 690     <varlistentry>
 691      <term><literal>$_TD-&gt;{old}{foo}</literal></term>
 692      <listitem>
 693       <para>
 694        <literal>OLD</literal> value of column <literal>foo</literal>
 695       </para>
 696      </listitem>
 697     </varlistentry>
 698
 699     <varlistentry>
 700      <term><literal>$_TD-&gt;{name}</literal></term>
 701      <listitem>
 702       <para>
 703        Name of the trigger being called
 704       </para>
 705      </listitem>
 706     </varlistentry>
 707
 708     <varlistentry>
 709      <term><literal>$_TD-&gt;{event}</literal></term>
 710      <listitem>
 711       <para>
 712        Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
 713       </para>
 714      </listitem>
 715     </varlistentry>
 716
 717     <varlistentry>
 718      <term><literal>$_TD-&gt;{when}</literal></term>
 719      <listitem>
 720       <para>
 721        When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
 722       </para>
 723      </listitem>
 724     </varlistentry>
 725
 726     <varlistentry>
 727      <term><literal>$_TD-&gt;{level}</literal></term>
 728      <listitem>
 729       <para>
 730        The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
 731       </para>
 732      </listitem>
 733     </varlistentry>
 734
 735     <varlistentry>
 736      <term><literal>$_TD-&gt;{relid}</literal></term>
 737      <listitem>
 738       <para>
 739        OID of the table on which the trigger fired
 740       </para>
 741      </listitem>
 742     </varlistentry>
 743
 744     <varlistentry>
 745      <term><literal>$_TD-&gt;{table_name}</literal></term>
 746      <listitem>
 747       <para>
 748        Name of the table on which the trigger fired
 749       </para>
 750      </listitem>
 751     </varlistentry>
 752
 753     <varlistentry>
 754      <term><literal>$_TD-&gt;{relname}</literal></term>
 755      <listitem>
 756       <para>
 757        Name of the table on which the trigger fired. This has been deprecated,
 758        and could be removed in a future release.
 759        Please use $_TD-&gt;{table_name} instead.
 760       </para>
 761      </listitem>
 762     </varlistentry>
 763
 764     <varlistentry>
 765      <term><literal>$_TD-&gt;{table_schema}</literal></term>
 766      <listitem>
 767       <para>
 768        Name of the schema in which the table on which the trigger fired, is
 769       </para>
 770      </listitem>
 771     </varlistentry>
 772
 773     <varlistentry>
 774      <term><literal>$_TD-&gt;{argc}</literal></term>
 775      <listitem>
 776       <para>
 777        Number of arguments of the trigger function
 778       </para>
 779      </listitem>
 780     </varlistentry>
 781
 782     <varlistentry>
 783      <term><literal>@{$_TD-&gt;{args}}</literal></term>
 784      <listitem>
 785       <para>
 786        Arguments of the trigger function.  Does not exist if <literal>$_TD-&gt;{argc}</literal> is 0.
 787       </para>
 788      </listitem>
 789     </varlistentry>
 790
 791    </variablelist>
 792   </para>
 793
 794   <para>
 795    Triggers can return one of the following:
 796
 797    <variablelist>
 798     <varlistentry>
 799      <term><literal>return;</literal></term>
 800      <listitem>
 801       <para>
 802        Execute the statement
 803       </para>
 804      </listitem>
 805     </varlistentry>
 806
 807     <varlistentry>
 808      <term><literal>"SKIP"</literal></term>
 809      <listitem>
 810       <para>
 811        Don't execute the statement
 812       </para>
 813      </listitem>
 814     </varlistentry>
 815
 816     <varlistentry>
 817      <term><literal>"MODIFY"</literal></term>
 818      <listitem>
 819       <para>
 820        Indicates that the <literal>NEW</literal> row was modified by
 821        the trigger function
 822       </para>
 823      </listitem>
 824     </varlistentry>
 825    </variablelist>
 826   </para>
 827
 828   <para>
 829    Here is an example of a trigger function, illustrating some of the
 830    above:
 831 <programlisting>
 832 CREATE TABLE test (
 833     i int,
 834     v varchar
 835 );
 836
 837 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
 838     if (($_TD-&gt;{new}{i} &gt;= 100) || ($_TD-&gt;{new}{i} &lt;= 0)) {
 839         return "SKIP";    # skip INSERT/UPDATE command
 840     } elsif ($_TD-&gt;{new}{v} ne "immortal") {
 841         $_TD-&gt;{new}{v} .= "(modified by trigger)";
 842         return "MODIFY";  # modify row and execute INSERT/UPDATE command
 843     } else {
 844         return;           # execute INSERT/UPDATE command
 845     }
 846 $$ LANGUAGE plperl;
 847
 848 CREATE TRIGGER test_valid_id_trig
 849     BEFORE INSERT OR UPDATE ON test
 850     FOR EACH ROW EXECUTE PROCEDURE valid_id();
 851 </programlisting>
 852   </para>
 853  </sect1>
 854
 855  <sect1 id="plperl-missing">
 856   <title>Limitations and Missing Features</title>
 857
 858   <para>
 859    The following features are currently missing from PL/Perl, but they
 860    would make welcome contributions.
 861
 862    <itemizedlist>
 863     <listitem>
 864      <para>
 865       PL/Perl functions cannot call each other directly (because they
 866       are anonymous subroutines inside Perl).
 867      </para>
 868     </listitem>
 869
 870     <listitem>
 871      <para>
 872       SPI is not yet fully implemented.
 873      </para>
 874     </listitem>
 875
 876     <listitem>
 877      <para>
 878       If you are fetching very large data sets using
 879       <literal>spi_exec_query</literal>, you should be aware that
 880       these will all go into memory.  You can avoid this by using
 881       <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
 882       illustrated earlier.
 883      </para>
 884      <para>
 885         A similar problem occurs if a set-returning function passes a
 886         large set of rows back to PostgreSQL via <literal>return</literal>. You
 887         can avoid this problem too by instead using
 888         <literal>return_next</literal> for each row returned, as shown
 889         previously.
 890      </para>
 891
 892     </listitem>
 893    </itemizedlist>
 894   </para>
 895  </sect1>
 896
 897 </chapter>