doc/src/sgml/plperl.sgml

   1 <!-- doc/src/sgml/plperl.sgml -->
   2
   3  <chapter id="plperl">
   4   <title>PL/Perl &mdash; 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 and procedures in the
  17    <ulink url="https://www.perl.org">Perl programming language</ulink>.
  18   </para>
  19
  20   <para>
  21    The main advantage to using PL/Perl is that this allows use,
  22    within stored functions and procedures, of the manyfold <quote>string
  23    munging</quote> operators and functions available for Perl.  Parsing
  24    complex strings might be easier using Perl than it is with the
  25    string functions and control structures provided in PL/pgSQL.
  26   </para>
  27
  28   <para>
  29    To install PL/Perl in a particular database, use
  30    <literal>CREATE EXTENSION plperl</literal>.
  31   </para>
  32
  33   <tip>
  34    <para>
  35     If a language is installed into <literal>template1</literal>, all subsequently
  36     created databases will have the language installed automatically.
  37    </para>
  38   </tip>
  39
  40   <note>
  41    <para>
  42     Users of source packages must specially enable the build of
  43     PL/Perl during the installation process.  (Refer to <xref
  44     linkend="installation"/> for more information.)  Users of
  45     binary packages might find PL/Perl in a separate subpackage.
  46    </para>
  47   </note>
  48
  49  <sect1 id="plperl-funcs">
  50   <title>PL/Perl Functions and Arguments</title>
  51
  52   <para>
  53    To create a function in the PL/Perl language, use the standard
  54    <xref linkend="sql-createfunction"/>
  55    syntax:
  56
  57 <programlisting>
  58 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>)
  59 RETURNS <replaceable>return-type</replaceable>
  60 -- function attributes can go here
  61 AS $$
  62     # PL/Perl function body goes here
  63 $$ LANGUAGE plperl;
  64 </programlisting>
  65
  66    The body of the function is ordinary Perl code. In fact, the PL/Perl
  67    glue code wraps it inside a Perl subroutine.  A PL/Perl function is
  68    called in a scalar context, so it can't return a list.  You can return
  69    non-scalar values (arrays, records, and sets) by returning a reference,
  70    as discussed below.
  71   </para>
  72
  73   <para>
  74    In a PL/Perl procedure, any return value from the Perl code is ignored.
  75   </para>
  76
  77   <para>
  78    PL/Perl also supports anonymous code blocks called with the
  79    <xref linkend="sql-do"/> statement:
  80
  81 <programlisting>
  82 DO $$
  83     # PL/Perl code
  84 $$ LANGUAGE plperl;
  85 </programlisting>
  86
  87    An anonymous code block receives no arguments, and whatever value it
  88    might return is discarded.  Otherwise it behaves just like a function.
  89   </para>
  90
  91   <note>
  92    <para>
  93     The use of named nested subroutines is dangerous in Perl, especially if
  94     they refer to lexical variables in the enclosing scope. Because a PL/Perl
  95     function is wrapped in a subroutine, any named subroutine you place inside
  96     one will be nested. In general, it is far safer to create anonymous
  97     subroutines which you call via a coderef. For more information, see the
  98     entries for <literal>Variable "%s" will not stay shared</literal> and
  99     <literal>Variable "%s" is not available</literal> in the
 100     <citerefentry><refentrytitle>perldiag</refentrytitle></citerefentry> man page, or
 101     search the Internet for <quote>perl nested named subroutine</quote>.
 102    </para>
 103   </note>
 104
 105   <para>
 106    The syntax of the <command>CREATE FUNCTION</command> command requires
 107    the function body to be written as a string constant.  It is usually
 108    most convenient to use dollar quoting (see <xref
 109    linkend="sql-syntax-dollar-quoting"/>) for the string constant.
 110    If you choose to use escape string syntax <literal>E''</literal>,
 111    you must double any single quote marks (<literal>'</literal>) and backslashes
 112    (<literal>\</literal>) used in the body of the function
 113    (see <xref linkend="sql-syntax-strings"/>).
 114   </para>
 115
 116   <para>
 117    Arguments and results are handled as in any other Perl subroutine:
 118    arguments are passed in <varname>@_</varname>, and a result value
 119    is returned with <literal>return</literal> or as the last expression
 120    evaluated in the function.
 121   </para>
 122
 123   <para>
 124    For example, a function returning the greater of two integer values
 125    could be defined as:
 126
 127 <programlisting>
 128 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
 129     if ($_[0] &gt; $_[1]) { return $_[0]; }
 130     return $_[1];
 131 $$ LANGUAGE plperl;
 132 </programlisting>
 133   </para>
 134
 135   <note>
 136     <para>
 137       Arguments will be converted from the database's encoding to UTF-8
 138       for use inside PL/Perl, and then converted from UTF-8 back to the
 139       database encoding upon return.
 140     </para>
 141   </note>
 142
 143   <para>
 144    If an SQL null value<indexterm><primary>null value</primary><secondary
 145    sortas="PL/Perl">in PL/Perl</secondary></indexterm> is passed to a function,
 146    the argument value will appear as <quote>undefined</quote> in Perl.  The
 147    above function definition will not behave very nicely with null
 148    inputs (in fact, it will act as though they are zeroes).  We could
 149    add <literal>STRICT</literal> to the function definition to make
 150    <productname>PostgreSQL</productname> do something more reasonable:
 151    if a null value is passed, the function will not be called at all,
 152    but will just return a null result automatically.  Alternatively,
 153    we could check for undefined inputs in the function body.  For
 154    example, suppose that we wanted <function>perl_max</function> with
 155    one null and one nonnull argument to return the nonnull argument,
 156    rather than a null value:
 157
 158 <programlisting>
 159 CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
 160     my ($x, $y) = @_;
 161     if (not defined $x) {
 162         return undef if not defined $y;
 163         return $y;
 164     }
 165     return $x if not defined $y;
 166     return $x if $x &gt; $y;
 167     return $y;
 168 $$ LANGUAGE plperl;
 169 </programlisting>
 170    As shown above, to return an SQL null value from a PL/Perl
 171    function, return an undefined value.  This can be done whether the
 172    function is strict or not.
 173   </para>
 174
 175   <para>
 176    Anything in a function argument that is not a reference is
 177    a string, which is in the standard <productname>PostgreSQL</productname>
 178    external text representation for the relevant data type. In the case of
 179    ordinary numeric or text types, Perl will just do the right thing and
 180    the programmer will normally not have to worry about it. However, in
 181    other cases the argument will need to be converted into a form that is
 182    more usable in Perl. For example, the <function>decode_bytea</function>
 183    function can be used to convert an argument of
 184    type <type>bytea</type> into unescaped binary.
 185   </para>
 186
 187   <para>
 188    Similarly, values passed back to <productname>PostgreSQL</productname>
 189    must be in the external text representation format. For example, the
 190    <function>encode_bytea</function> function can be used to
 191    escape binary data for a return value of type <type>bytea</type>.
 192   </para>
 193
 194   <para>
 195    One case that is particularly important is boolean values.  As just
 196    stated, the default behavior for <type>bool</type> values is that they
 197    are passed to Perl as text, thus either <literal>'t'</literal>
 198    or <literal>'f'</literal>.  This is problematic, since Perl will not
 199    treat <literal>'f'</literal> as false!  It is possible to improve matters
 200    by using a <quote>transform</quote> (see
 201    <xref linkend="sql-createtransform"/>).  Suitable transforms are provided
 202    by the <filename>bool_plperl</filename> extension.  To use it, install
 203    the extension:
 204 <programlisting>
 205 CREATE EXTENSION bool_plperl;  -- or bool_plperlu for PL/PerlU
 206 </programlisting>
 207    Then use the <literal>TRANSFORM</literal> function attribute for a
 208    PL/Perl function that takes or returns <type>bool</type>, for example:
 209 <programlisting>
 210 CREATE FUNCTION perl_and(bool, bool) RETURNS bool
 211 TRANSFORM FOR TYPE bool
 212 AS $$
 213   my ($a, $b) = @_;
 214   return $a &amp;&amp; $b;
 215 $$ LANGUAGE plperl;
 216 </programlisting>
 217    When this transform is applied, <type>bool</type> arguments will be seen
 218    by Perl as being <literal>1</literal> or empty, thus properly true or
 219    false.  If the function result is type <type>bool</type>, it will be true
 220    or false according to whether Perl would evaluate the returned value as
 221    true.
 222    Similar transformations are also performed for boolean query arguments
 223    and results of SPI queries performed inside the function
 224    (<xref linkend="plperl-database"/>).
 225   </para>
 226
 227   <para>
 228    Perl can return <productname>PostgreSQL</productname> arrays as
 229    references to Perl arrays.  Here is an example:
 230
 231 <programlisting>
 232 CREATE OR REPLACE function returns_array()
 233 RETURNS text[][] AS $$
 234     return [['a&quot;b','c,d'],['e\\f','g']];
 235 $$ LANGUAGE plperl;
 236
 237 select returns_array();
 238 </programlisting>
 239   </para>
 240
 241   <para>
 242    Perl passes <productname>PostgreSQL</productname> arrays as a blessed
 243    <type>PostgreSQL::InServer::ARRAY</type> object. This object may be treated as an array
 244    reference or a string, allowing for backward compatibility with Perl
 245    code written for <productname>PostgreSQL</productname> versions below 9.1 to
 246    run.  For example:
 247
 248 <programlisting>
 249 CREATE OR REPLACE FUNCTION concat_array_elements(text[]) RETURNS TEXT AS $$
 250     my $arg = shift;
 251     my $result = "";
 252     return undef if (!defined $arg);
 253
 254     # as an array reference
 255     for (@$arg) {
 256         $result .= $_;
 257     }
 258
 259     # also works as a string
 260     $result .= $arg;
 261
 262     return $result;
 263 $$ LANGUAGE plperl;
 264
 265 SELECT concat_array_elements(ARRAY['PL','/','Perl']);
 266 </programlisting>
 267
 268   <note>
 269    <para>
 270     Multidimensional arrays are represented as references to
 271     lower-dimensional arrays of references in a way common to every Perl
 272     programmer.
 273    </para>
 274   </note>
 275   </para>
 276
 277   <para>
 278    Composite-type arguments are passed to the function as references
 279    to hashes.  The keys of the hash are the attribute names of the
 280    composite type.  Here is an example:
 281
 282 <programlisting>
 283 CREATE TABLE employee (
 284     name text,
 285     basesalary integer,
 286     bonus integer
 287 );
 288
 289 CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
 290     my ($emp) = @_;
 291     return $emp-&gt;{basesalary} + $emp-&gt;{bonus};
 292 $$ LANGUAGE plperl;
 293
 294 SELECT name, empcomp(employee.*) FROM employee;
 295 </programlisting>
 296   </para>
 297
 298   <para>
 299    A PL/Perl function can return a composite-type result using the same
 300    approach: return a reference to a hash that has the required attributes.
 301    For example:
 302
 303 <programlisting>
 304 CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
 305
 306 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
 307     return {f2 =&gt; 'hello', f1 =&gt; 1, f3 =&gt; 'world'};
 308 $$ LANGUAGE plperl;
 309
 310 SELECT * FROM perl_row();
 311 </programlisting>
 312
 313    Any columns in the declared result data type that are not present in the
 314    hash will be returned as null values.
 315   </para>
 316
 317   <para>
 318    Similarly, output arguments of procedures can be returned as a hash
 319    reference:
 320
 321 <programlisting>
 322 CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
 323     my ($a, $b) = @_;
 324     return {a =&gt; $a * 3, b =&gt; $b * 3};
 325 $$ LANGUAGE plperl;
 326
 327 CALL perl_triple(5, 10);
 328 </programlisting>
 329   </para>
 330
 331   <para>
 332     PL/Perl functions can also return sets of either scalar or
 333     composite types.  Usually you'll want to return rows one at a
 334     time, both to speed up startup time and to keep from queuing up
 335     the entire result set in memory.  You can do this with
 336     <function>return_next</function> as illustrated below.  Note that
 337     after the last <function>return_next</function>, you must put
 338     either <literal>return</literal> or (better) <literal>return
 339     undef</literal>.
 340
 341 <programlisting>
 342 CREATE OR REPLACE FUNCTION perl_set_int(int)
 343 RETURNS SETOF INTEGER AS $$
 344     foreach (0..$_[0]) {
 345         return_next($_);
 346     }
 347     return undef;
 348 $$ LANGUAGE plperl;
 349
 350 SELECT * FROM perl_set_int(5);
 351
 352 CREATE OR REPLACE FUNCTION perl_set()
 353 RETURNS SETOF testrowperl AS $$
 354     return_next({ f1 =&gt; 1, f2 =&gt; 'Hello', f3 =&gt; 'World' });
 355     return_next({ f1 =&gt; 2, f2 =&gt; 'Hello', f3 =&gt; 'PostgreSQL' });
 356     return_next({ f1 =&gt; 3, f2 =&gt; 'Hello', f3 =&gt; 'PL/Perl' });
 357     return undef;
 358 $$ LANGUAGE plperl;
 359 </programlisting>
 360
 361     For small result sets, you can return a reference to an array that
 362     contains either scalars, references to arrays, or references to
 363     hashes for simple types, array types, and composite types,
 364     respectively.  Here are some simple examples of returning the entire
 365     result set as an array reference:
 366
 367 <programlisting>
 368 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
 369     return [0..$_[0]];
 370 $$ LANGUAGE plperl;
 371
 372 SELECT * FROM perl_set_int(5);
 373
 374 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
 375     return [
 376         { f1 =&gt; 1, f2 =&gt; 'Hello', f3 =&gt; 'World' },
 377         { f1 =&gt; 2, f2 =&gt; 'Hello', f3 =&gt; 'PostgreSQL' },
 378         { f1 =&gt; 3, f2 =&gt; 'Hello', f3 =&gt; 'PL/Perl' }
 379     ];
 380 $$ LANGUAGE plperl;
 381
 382 SELECT * FROM perl_set();
 383 </programlisting>
 384   </para>
 385
 386   <para>
 387    If you wish to use the <literal>strict</literal> pragma with your code you
 388    have a few options. For temporary global use you can <command>SET</command>
 389    <literal>plperl.use_strict</literal> to true.
 390    This will affect subsequent compilations of <application>PL/Perl</application>
 391    functions, but not functions already compiled in the current session.
 392    For permanent global use you can set <literal>plperl.use_strict</literal>
 393    to true in the <filename>postgresql.conf</filename> file.
 394   </para>
 395
 396   <para>
 397    For permanent use in specific functions you can simply put:
 398 <programlisting>
 399 use strict;
 400 </programlisting>
 401    at the top of the function body.
 402   </para>
 403
 404   <para>
 405   The <literal>feature</literal> pragma is also available to <function>use</function> if your Perl is version 5.10.0 or higher.
 406   </para>
 407
 408  </sect1>
 409
 410  <sect1 id="plperl-data">
 411   <title>Data Values in PL/Perl</title>
 412
 413   <para>
 414    The argument values supplied to a PL/Perl function's code are
 415    simply the input arguments converted to text form (just as if they
 416    had been displayed by a <command>SELECT</command> statement).
 417    Conversely, the <function>return</function> and <function>return_next</function>
 418    commands will accept any string that is acceptable input format
 419    for the function's declared return type.
 420   </para>
 421
 422   <para>
 423    If this behavior is inconvenient for a particular case, it can be
 424    improved by using a transform, as already illustrated
 425    for <type>bool</type> values.  Several examples of transform modules
 426    are included in the <productname>PostgreSQL</productname> distribution.
 427   </para>
 428  </sect1>
 429
 430  <sect1 id="plperl-builtins">
 431   <title>Built-in Functions</title>
 432
 433  <sect2 id="plperl-database">
 434   <title>Database Access from PL/Perl</title>
 435
 436   <para>
 437    Access to the database itself from your Perl function can be done
 438    via the following functions:
 439   </para>
 440
 441    <variablelist>
 442     <varlistentry>
 443      <term>
 444       <literal><function>spi_exec_query</function>(<replaceable>query</replaceable> [, <replaceable>limit</replaceable>])</literal>
 445       <indexterm>
 446        <primary>spi_exec_query</primary>
 447        <secondary>in PL/Perl</secondary>
 448       </indexterm>
 449      </term>
 450      <listitem>
 451       <para>
 452        <function>spi_exec_query</function> executes an SQL command and
 453 returns the entire row set as a reference to an array of hash references.
 454 If <replaceable>limit</replaceable> is specified and is greater than zero,
 455 then <function>spi_exec_query</function> retrieves at
 456 most <replaceable>limit</replaceable> rows, much as if the query included
 457 a <literal>LIMIT</literal> clause.  Omitting <replaceable>limit</replaceable>
 458 or specifying it as zero results in no row limit.
 459       </para>
 460
 461       <para>
 462 <emphasis>You should only use this command when you know
 463 that the result set will be relatively small.</emphasis>  Here is an
 464 example of a query (<command>SELECT</command> command) with the
 465 optional maximum number of rows:
 466
 467 <programlisting>
 468 $rv = spi_exec_query('SELECT * FROM my_table', 5);
 469 </programlisting>
 470         This returns up to 5 rows from the table
 471         <literal>my_table</literal>.  If <literal>my_table</literal>
 472         has a column <literal>my_column</literal>, you can get that
 473         value from row <literal>$i</literal> of the result like this:
 474 <programlisting>
 475 $foo = $rv-&gt;{rows}[$i]-&gt;{my_column};
 476 </programlisting>
 477        The total number of rows returned from a <command>SELECT</command>
 478        query can be accessed like this:
 479 <programlisting>
 480 $nrows = $rv-&gt;{processed}
 481 </programlisting>
 482       </para>
 483
 484       <para>
 485        Here is an example using a different command type:
 486 <programlisting>
 487 $query = "INSERT INTO my_table VALUES (1, 'test')";
 488 $rv = spi_exec_query($query);
 489 </programlisting>
 490        You can then access the command status (e.g.,
 491        <literal>SPI_OK_INSERT</literal>) like this:
 492 <programlisting>
 493 $res = $rv-&gt;{status};
 494 </programlisting>
 495        To get the number of rows affected, do:
 496 <programlisting>
 497 $nrows = $rv-&gt;{processed};
 498 </programlisting>
 499       </para>
 500
 501       <para>
 502        Here is a complete example:
 503 <programlisting>
 504 CREATE TABLE test (
 505     i int,
 506     v varchar
 507 );
 508
 509 INSERT INTO test (i, v) VALUES (1, 'first line');
 510 INSERT INTO test (i, v) VALUES (2, 'second line');
 511 INSERT INTO test (i, v) VALUES (3, 'third line');
 512 INSERT INTO test (i, v) VALUES (4, 'immortal');
 513
 514 CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
 515     my $rv = spi_exec_query('select i, v from test;');
 516     my $status = $rv-&gt;{status};
 517     my $nrows = $rv-&gt;{processed};
 518     foreach my $rn (0 .. $nrows - 1) {
 519         my $row = $rv-&gt;{rows}[$rn];
 520         $row-&gt;{i} += 200 if defined($row-&gt;{i});
 521         $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));
 522         return_next($row);
 523     }
 524     return undef;
 525 $$ LANGUAGE plperl;
 526
 527 SELECT * FROM test_munge();
 528 </programlisting>
 529     </para>
 530     </listitem>
 531     </varlistentry>
 532
 533     <varlistentry>
 534      <term>
 535       <literal><function>spi_query(<replaceable>command</replaceable>)</function></literal>
 536       <indexterm>
 537        <primary>spi_query</primary>
 538        <secondary>in PL/Perl</secondary>
 539       </indexterm>
 540      </term>
 541      <term>
 542       <literal><function>spi_fetchrow(<replaceable>cursor</replaceable>)</function></literal>
 543       <indexterm>
 544        <primary>spi_fetchrow</primary>
 545        <secondary>in PL/Perl</secondary>
 546       </indexterm>
 547      </term>
 548      <term>
 549       <literal><function>spi_cursor_close(<replaceable>cursor</replaceable>)</function></literal>
 550       <indexterm>
 551        <primary>spi_cursor_close</primary>
 552        <secondary>in PL/Perl</secondary>
 553       </indexterm>
 554      </term>
 555
 556     <listitem>
 557     <para>
 558     <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
 559     work together as a pair for row sets which might be large, or for cases
 560     where you wish to return rows as they arrive.
 561     <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
 562     <literal>spi_query</literal>. The following example illustrates how
 563     you use them together:
 564
 565 <programlisting>
 566 CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
 567
 568 CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
 569     use Digest::MD5 qw(md5_hex);
 570     my $file = '/usr/share/dict/words';
 571     my $t = localtime;
 572     elog(NOTICE, "opening file $file at $t" );
 573     open my $fh, '&lt;', $file # ooh, it's a file access!
 574         or elog(ERROR, "cannot open $file for reading: $!");
 575     my @words = &lt;$fh&gt;;
 576     close $fh;
 577     $t = localtime;
 578     elog(NOTICE, "closed file $file at $t");
 579     chomp(@words);
 580     my $row;
 581     my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
 582     while (defined ($row = spi_fetchrow($sth))) {
 583         return_next({
 584             the_num =&gt; $row-&gt;{a},
 585             the_text =&gt; md5_hex($words[rand @words])
 586         });
 587     }
 588     return;
 589 $$ LANGUAGE plperlu;
 590
 591 SELECT * from lotsa_md5(500);
 592 </programlisting>
 593     </para>
 594
 595     <para>
 596      Normally, <function>spi_fetchrow</function> should be repeated until it
 597      returns <literal>undef</literal>, indicating that there are no more
 598      rows to read.  The cursor returned by <literal>spi_query</literal>
 599      is automatically freed when
 600      <function>spi_fetchrow</function> returns <literal>undef</literal>.
 601      If you do not wish to read all the rows, instead call
 602      <function>spi_cursor_close</function> to free the cursor.
 603      Failure to do so will result in memory leaks.
 604     </para>
 605
 606     </listitem>
 607     </varlistentry>
 608
 609     <varlistentry>
 610      <term>
 611       <literal><function>spi_prepare(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</function></literal>
 612       <indexterm>
 613        <primary>spi_prepare</primary>
 614        <secondary>in PL/Perl</secondary>
 615      </indexterm>
 616      </term>
 617      <term>
 618       <literal><function>spi_query_prepared(<replaceable>plan</replaceable>, <replaceable>arguments</replaceable>)</function></literal>
 619       <indexterm>
 620        <primary>spi_query_prepared</primary>
 621        <secondary>in PL/Perl</secondary>
 622       </indexterm>
 623      </term>
 624      <term>
 625       <literal><function>spi_exec_prepared(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</function></literal>
 626       <indexterm>
 627        <primary>spi_exec_prepared</primary>
 628        <secondary>in PL/Perl</secondary>
 629       </indexterm>
 630      </term>
 631      <term>
 632       <literal><function>spi_freeplan(<replaceable>plan</replaceable>)</function></literal>
 633       <indexterm>
 634        <primary>spi_freeplan</primary>
 635        <secondary>in PL/Perl</secondary>
 636       </indexterm>
 637      </term>
 638
 639     <listitem>
 640     <para>
 641     <literal>spi_prepare</literal>, <literal>spi_query_prepared</literal>, <literal>spi_exec_prepared</literal>,
 642     and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries.
 643     <literal>spi_prepare</literal> accepts a query string with numbered argument placeholders ($1, $2, etc.)
 644     and a string list of argument types:
 645 <programlisting>
 646 $plan = spi_prepare('SELECT * FROM test WHERE id &gt; $1 AND name = $2',
 647                                                      'INTEGER', 'TEXT');
 648 </programlisting>
 649     Once a query plan is prepared by a call to <literal>spi_prepare</literal>, the plan can be used instead
 650     of the string query, either in <literal>spi_exec_prepared</literal>, where the result is the same as returned
 651     by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepared</literal> which returns a cursor
 652     exactly as <literal>spi_query</literal> does, which can be later passed to <literal>spi_fetchrow</literal>.
 653     The optional second parameter to <literal>spi_exec_prepared</literal> is a hash reference of attributes;
 654     the only attribute currently supported is <literal>limit</literal>, which
 655     sets the maximum number of rows returned from the query.
 656     Omitting <literal>limit</literal> or specifying it as zero results in no
 657     row limit.
 658     </para>
 659
 660     <para>
 661     The advantage of prepared queries is that is it possible to use one prepared plan for more
 662     than one query execution. After the plan is not needed anymore, it can be freed with
 663     <literal>spi_freeplan</literal>:
 664 <programlisting>
 665 CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
 666         $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',
 667                                         'INTERVAL');
 668 $$ LANGUAGE plperl;
 669
 670 CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
 671         return spi_exec_prepared(
 672                 $_SHARED{my_plan},
 673                 $_[0]
 674         )->{rows}->[0]->{now};
 675 $$ LANGUAGE plperl;
 676
 677 CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
 678         spi_freeplan( $_SHARED{my_plan});
 679         undef $_SHARED{my_plan};
 680 $$ LANGUAGE plperl;
 681
 682 SELECT init();
 683 SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
 684 SELECT done();
 685
 686   add_time  |  add_time  |  add_time
 687 ------------+------------+------------
 688  2005-12-10 | 2005-12-11 | 2005-12-12
 689 </programlisting>
 690     Note that the parameter subscript in <literal>spi_prepare</literal> is defined via
 691     $1, $2, $3, etc., so avoid declaring query strings in double quotes that might easily
 692     lead to hard-to-catch bugs.
 693     </para>
 694
 695     <para>
 696     Another example illustrates usage of an optional parameter in <literal>spi_exec_prepared</literal>:
 697 <programlisting>
 698 CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address
 699                       FROM generate_series(1,3) AS id;
 700
 701 CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
 702         $_SHARED{plan} = spi_prepare('SELECT * FROM hosts
 703                                       WHERE address &lt;&lt; $1', 'inet');
 704 $$ LANGUAGE plperl;
 705
 706 CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
 707         return spi_exec_prepared(
 708                 $_SHARED{plan},
 709                 {limit =&gt; 2},
 710                 $_[0]
 711         )->{rows};
 712 $$ LANGUAGE plperl;
 713
 714 CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
 715         spi_freeplan($_SHARED{plan});
 716         undef $_SHARED{plan};
 717 $$ LANGUAGE plperl;
 718
 719 SELECT init_hosts_query();
 720 SELECT query_hosts('192.168.1.0/30');
 721 SELECT release_hosts_query();
 722
 723     query_hosts
 724 -----------------
 725  (1,192.168.1.1)
 726  (2,192.168.1.2)
 727 (2 rows)
 728 </programlisting>
 729     </para>
 730     </listitem>
 731     </varlistentry>
 732
 733     <varlistentry>
 734      <term>
 735       <literal><function>spi_commit()</function></literal>
 736       <indexterm>
 737        <primary>spi_commit</primary>
 738        <secondary>in PL/Perl</secondary>
 739      </indexterm>
 740      </term>
 741      <term>
 742       <literal><function>spi_rollback()</function></literal>
 743       <indexterm>
 744        <primary>spi_rollback</primary>
 745        <secondary>in PL/Perl</secondary>
 746       </indexterm>
 747      </term>
 748      <listitem>
 749       <para>
 750        Commit or roll back the current transaction.  This can only be called
 751        in a procedure or anonymous code block (<command>DO</command> command)
 752        called from the top level.  (Note that it is not possible to run the
 753        SQL commands <command>COMMIT</command> or <command>ROLLBACK</command>
 754        via <function>spi_exec_query</function> or similar.  It has to be done
 755        using these functions.)  After a transaction is ended, a new
 756        transaction is automatically started, so there is no separate function
 757        for that.
 758       </para>
 759
 760       <para>
 761        Here is an example:
 762 <programlisting>
 763 CREATE PROCEDURE transaction_test1()
 764 LANGUAGE plperl
 765 AS $$
 766 foreach my $i (0..9) {
 767     spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
 768     if ($i % 2 == 0) {
 769         spi_commit();
 770     } else {
 771         spi_rollback();
 772     }
 773 }
 774 $$;
 775
 776 CALL transaction_test1();
 777 </programlisting>
 778       </para>
 779      </listitem>
 780     </varlistentry>
 781    </variablelist>
 782  </sect2>
 783
 784  <sect2 id="plperl-utility-functions">
 785   <title>Utility Functions in PL/Perl</title>
 786
 787    <variablelist>
 788     <varlistentry>
 789      <term>
 790       <literal><function>elog(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</function></literal>
 791       <indexterm>
 792        <primary>elog</primary>
 793        <secondary>in PL/Perl</secondary>
 794       </indexterm>
 795      </term>
 796      <listitem>
 797       <para>
 798        Emit a log or error message. Possible levels are
 799        <literal>DEBUG</literal>, <literal>LOG</literal>, <literal>INFO</literal>,
 800        <literal>NOTICE</literal>, <literal>WARNING</literal>, and <literal>ERROR</literal>.
 801        <literal>ERROR</literal>
 802         raises an error condition; if this is not trapped by the surrounding
 803         Perl code, the error propagates out to the calling query, causing
 804         the current transaction or subtransaction to be aborted.  This
 805         is effectively the same as the Perl <literal>die</literal> command.
 806         The other levels only generate messages of different
 807         priority levels.
 808         Whether messages of a particular priority are reported to the client,
 809         written to the server log, or both is controlled by the
 810         <xref linkend="guc-log-min-messages"/> and
 811         <xref linkend="guc-client-min-messages"/> configuration
 812         variables. See <xref linkend="runtime-config"/> for more
 813         information.
 814       </para>
 815      </listitem>
 816     </varlistentry>
 817
 818     <varlistentry>
 819      <term>
 820       <literal><function>quote_literal(<replaceable>string</replaceable>)</function></literal>
 821       <indexterm>
 822        <primary>quote_literal</primary>
 823        <secondary>in PL/Perl</secondary>
 824       </indexterm>
 825      </term>
 826      <listitem>
 827       <para>
 828         Return the given string suitably quoted to be used as a string literal in an SQL
 829         statement string. Embedded single-quotes and backslashes are properly doubled.
 830         Note that <function>quote_literal</function> returns undef on undef input; if the argument
 831         might be undef, <function>quote_nullable</function> is often more suitable.
 832       </para>
 833      </listitem>
 834     </varlistentry>
 835
 836     <varlistentry>
 837      <term>
 838       <literal><function>quote_nullable(<replaceable>string</replaceable>)</function></literal>
 839       <indexterm>
 840        <primary>quote_nullable</primary>
 841        <secondary>in PL/Perl</secondary>
 842       </indexterm>
 843      </term>
 844      <listitem>
 845       <para>
 846         Return the given string suitably quoted to be used as a string literal in an SQL
 847         statement string; or, if the argument is undef, return the unquoted string "NULL".
 848         Embedded single-quotes and backslashes are properly doubled.
 849       </para>
 850      </listitem>
 851     </varlistentry>
 852
 853     <varlistentry>
 854      <term>
 855       <literal><function>quote_ident(<replaceable>string</replaceable>)</function></literal>
 856       <indexterm>
 857        <primary>quote_ident</primary>
 858        <secondary>in PL/Perl</secondary>
 859       </indexterm>
 860      </term>
 861      <listitem>
 862       <para>
 863         Return the given string suitably quoted to be used as an identifier in
 864         an SQL statement string. Quotes are added only if necessary (i.e., if
 865         the string contains non-identifier characters or would be case-folded).
 866         Embedded quotes are properly doubled.
 867       </para>
 868      </listitem>
 869     </varlistentry>
 870
 871     <varlistentry>
 872      <term>
 873       <literal><function>decode_bytea(<replaceable>string</replaceable>)</function></literal>
 874       <indexterm>
 875        <primary>decode_bytea</primary>
 876        <secondary>in PL/Perl</secondary>
 877       </indexterm>
 878      </term>
 879      <listitem>
 880       <para>
 881         Return the unescaped binary data represented by the contents of the given string,
 882         which should be <type>bytea</type> encoded.
 883         </para>
 884      </listitem>
 885     </varlistentry>
 886
 887     <varlistentry>
 888      <term>
 889       <literal><function>encode_bytea(<replaceable>string</replaceable>)</function></literal>
 890       <indexterm>
 891        <primary>encode_bytea</primary>
 892        <secondary>in PL/Perl</secondary>
 893       </indexterm>
 894      </term>
 895      <listitem>
 896       <para>
 897         Return the <type>bytea</type> encoded form of the binary data contents of the given string.
 898         </para>
 899      </listitem>
 900     </varlistentry>
 901
 902     <varlistentry>
 903      <term>
 904       <literal><function>encode_array_literal(<replaceable>array</replaceable>)</function></literal>
 905       <indexterm>
 906        <primary>encode_array_literal</primary>
 907        <secondary>in PL/Perl</secondary>
 908       </indexterm>
 909      </term>
 910      <term>
 911       <literal><function>encode_array_literal(<replaceable>array</replaceable>, <replaceable>delimiter</replaceable>)</function></literal>
 912      </term>
 913      <listitem>
 914       <para>
 915         Returns the contents of the referenced array as a string in array literal format
 916         (see <xref linkend="arrays-input"/>).
 917         Returns the argument value unaltered if it's not a reference to an array.
 918         The delimiter used between elements of the array literal defaults to "<literal>, </literal>"
 919         if a delimiter is not specified or is undef.
 920         </para>
 921      </listitem>
 922     </varlistentry>
 923
 924     <varlistentry>
 925      <term>
 926       <literal><function>encode_typed_literal(<replaceable>value</replaceable>, <replaceable>typename</replaceable>)</function></literal>
 927       <indexterm>
 928        <primary>encode_typed_literal</primary>
 929        <secondary>in PL/Perl</secondary>
 930       </indexterm>
 931      </term>
 932       <listitem>
 933        <para>
 934          Converts a Perl variable to the value of the data type passed as a
 935          second argument and returns a string representation of this value.
 936          Correctly handles nested arrays and values of composite types.
 937        </para>
 938       </listitem>
 939     </varlistentry>
 940
 941     <varlistentry>
 942      <term>
 943       <literal><function>encode_array_constructor(<replaceable>array</replaceable>)</function></literal>
 944       <indexterm>
 945        <primary>encode_array_constructor</primary>
 946        <secondary>in PL/Perl</secondary>
 947       </indexterm>
 948      </term>
 949      <listitem>
 950       <para>
 951         Returns the contents of the referenced array as a string in array constructor format
 952         (see <xref linkend="sql-syntax-array-constructors"/>).
 953         Individual values are quoted using <function>quote_nullable</function>.
 954         Returns the argument value, quoted using <function>quote_nullable</function>,
 955         if it's not a reference to an array.
 956         </para>
 957      </listitem>
 958     </varlistentry>
 959
 960     <varlistentry>
 961      <term>
 962       <literal><function>looks_like_number(<replaceable>string</replaceable>)</function></literal>
 963       <indexterm>
 964        <primary>looks_like_number</primary>
 965        <secondary>in PL/Perl</secondary>
 966       </indexterm>
 967      </term>
 968      <listitem>
 969       <para>
 970         Returns a true value if the content of the given string looks like a
 971         number, according to Perl, returns false otherwise.
 972         Returns undef if the argument is undef.  Leading and trailing space is
 973         ignored. <literal>Inf</literal> and <literal>Infinity</literal> are regarded as numbers.
 974         </para>
 975      </listitem>
 976     </varlistentry>
 977
 978     <varlistentry>
 979      <term>
 980       <literal><function>is_array_ref(<replaceable>argument</replaceable>)</function></literal>
 981       <indexterm>
 982        <primary>is_array_ref</primary>
 983        <secondary>in PL/Perl</secondary>
 984       </indexterm>
 985      </term>
 986      <listitem>
 987       <para>
 988         Returns a true value if the given argument may be treated as an
 989         array reference, that is, if ref of the argument is <literal>ARRAY</literal> or
 990         <literal>PostgreSQL::InServer::ARRAY</literal>.  Returns false otherwise.
 991       </para>
 992      </listitem>
 993     </varlistentry>
 994
 995    </variablelist>
 996   </sect2>
 997  </sect1>
 998
 999  <sect1 id="plperl-global">
1000   <title>Global Values in PL/Perl</title>
1001
1002   <para>
1003     You can use the global hash <varname>%_SHARED</varname> to store
1004     data, including code references, between function calls for the
1005     lifetime of the current session.
1006   </para>
1007
1008   <para>
1009     Here is a simple example for shared data:
1010 <programlisting>
1011 CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
1012     if ($_SHARED{$_[0]} = $_[1]) {
1013         return 'ok';
1014     } else {
1015         return "cannot set shared variable $_[0] to $_[1]";
1016     }
1017 $$ LANGUAGE plperl;
1018
1019 CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
1020     return $_SHARED{$_[0]};
1021 $$ LANGUAGE plperl;
1022
1023 SELECT set_var('sample', 'Hello, PL/Perl!  How''s tricks?');
1024 SELECT get_var('sample');
1025 </programlisting>
1026   </para>
1027
1028   <para>
1029    Here is a slightly more complicated example using a code reference:
1030
1031 <programlisting>
1032 CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
1033     $_SHARED{myquote} = sub {
1034         my $arg = shift;
1035         $arg =~ s/(['\\])/\\$1/g;
1036         return "'$arg'";
1037     };
1038 $$ LANGUAGE plperl;
1039
1040 SELECT myfuncs(); /* initializes the function */
1041
1042 /* Set up a function that uses the quote function */
1043
1044 CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
1045     my $text_to_quote = shift;
1046     my $qfunc = $_SHARED{myquote};
1047     return &amp;$qfunc($text_to_quote);
1048 $$ LANGUAGE plperl;
1049 </programlisting>
1050
1051    (You could have replaced the above with the one-liner
1052    <literal>return $_SHARED{myquote}-&gt;($_[0]);</literal>
1053    at the expense of readability.)
1054   </para>
1055
1056   <para>
1057    For security reasons, PL/Perl executes functions called by any one SQL role
1058    in a separate Perl interpreter for that role.  This prevents accidental or
1059    malicious interference by one user with the behavior of another user's
1060    PL/Perl functions.  Each such interpreter has its own value of the
1061    <varname>%_SHARED</varname> variable and other global state.  Thus, two
1062    PL/Perl functions will share the same value of <varname>%_SHARED</varname>
1063    if and only if they are executed by the same SQL role.  In an application
1064    wherein a single session executes code under multiple SQL roles (via
1065    <literal>SECURITY DEFINER</literal> functions, use of <command>SET ROLE</command>, etc.)
1066    you may need to take explicit steps to ensure that PL/Perl functions can
1067    share data via <varname>%_SHARED</varname>.  To do that, make sure that
1068    functions that should communicate are owned by the same user, and mark
1069    them <literal>SECURITY DEFINER</literal>.  You must of course take care that
1070    such functions can't be used to do anything unintended.
1071   </para>
1072  </sect1>
1073
1074  <sect1 id="plperl-trusted">
1075   <title>Trusted and Untrusted PL/Perl</title>
1076
1077   <indexterm zone="plperl-trusted">
1078    <primary>trusted</primary>
1079    <secondary>PL/Perl</secondary>
1080   </indexterm>
1081
1082   <para>
1083    Normally, PL/Perl is installed as a <quote>trusted</quote> programming
1084    language named <literal>plperl</literal>.  In this setup, certain Perl
1085    operations are disabled to preserve security.  In general, the
1086    operations that are restricted are those that interact with the
1087    environment. This includes file handle operations,
1088    <literal>require</literal>, and <literal>use</literal> (for
1089    external modules).  There is no way to access internals of the
1090    database server process or to gain OS-level access with the
1091    permissions of the server process,
1092    as a C function can do.  Thus, any unprivileged database user can
1093    be permitted to use this language.
1094   </para>
1095
1096   <warning>
1097    <para>
1098     Trusted PL/Perl relies on the Perl <literal>Opcode</literal> module to
1099     preserve security.
1100     Perl
1101     <ulink url="https://perldoc.perl.org/Opcode#WARNING">documents</ulink>
1102     that the module is not effective for the trusted PL/Perl use case.  If
1103     your security needs are incompatible with the uncertainty in that warning,
1104     consider executing <literal>REVOKE USAGE ON LANGUAGE plperl FROM
1105     PUBLIC</literal>.
1106    </para>
1107   </warning>
1108
1109   <para>
1110    Here is an example of a function that will not work because file
1111    system operations are not allowed for security reasons:
1112 <programlisting>
1113 CREATE FUNCTION badfunc() RETURNS integer AS $$
1114     my $tmpfile = "/tmp/badfile";
1115     open my $fh, '&gt;', $tmpfile
1116         or elog(ERROR, qq{could not open the file "$tmpfile": $!});
1117     print $fh "Testing writing to a file\n";
1118     close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
1119     return 1;
1120 $$ LANGUAGE plperl;
1121 </programlisting>
1122     The creation of this function will fail as its use of a forbidden
1123     operation will be caught by the validator.
1124   </para>
1125
1126   <para>
1127    Sometimes it is desirable to write Perl functions that are not
1128    restricted.  For example, one might want a Perl function that sends
1129    mail.  To handle these cases, PL/Perl can also be installed as an
1130    <quote>untrusted</quote> language (usually called
1131    <application>PL/PerlU</application><indexterm><primary>PL/PerlU</primary></indexterm>).
1132    In this case the full Perl language is available.  When installing the
1133    language, the language name <literal>plperlu</literal> will select
1134    the untrusted PL/Perl variant.
1135   </para>
1136
1137   <para>
1138    The writer of a <application>PL/PerlU</application> function must take care that the function
1139    cannot be used to do anything unwanted, since it will be able to do
1140    anything that could be done by a user logged in as the database
1141    administrator.  Note that the database system allows only database
1142    superusers to create functions in untrusted languages.
1143   </para>
1144
1145   <para>
1146    If the above function was created by a superuser using the language
1147    <literal>plperlu</literal>, execution would succeed.
1148   </para>
1149
1150   <para>
1151    In the same way, anonymous code blocks written in Perl can use
1152    restricted operations if the language is specified as
1153    <literal>plperlu</literal> rather than <literal>plperl</literal>, but the caller
1154    must be a superuser.
1155   </para>
1156
1157   <note>
1158    <para>
1159     While <application>PL/Perl</application> functions run in a separate Perl
1160     interpreter for each SQL role, all <application>PL/PerlU</application> functions
1161     executed in a given session run in a single Perl interpreter (which is
1162     not any of the ones used for <application>PL/Perl</application> functions).
1163     This allows <application>PL/PerlU</application> functions to share data freely,
1164     but no communication can occur between <application>PL/Perl</application> and
1165     <application>PL/PerlU</application> functions.
1166    </para>
1167   </note>
1168
1169   <note>
1170    <para>
1171     Perl cannot support multiple interpreters within one process unless
1172     it was built with the appropriate flags, namely either
1173     <literal>usemultiplicity</literal> or <literal>useithreads</literal>.
1174     (<literal>usemultiplicity</literal> is preferred unless you actually need
1175     to use threads.  For more details, see the
1176     <citerefentry><refentrytitle>perlembed</refentrytitle></citerefentry> man page.)
1177     If <application>PL/Perl</application> is used with a copy of Perl that was not built
1178     this way, then it is only possible to have one Perl interpreter per
1179     session, and so any one session can only execute either
1180     <application>PL/PerlU</application> functions, or <application>PL/Perl</application> functions
1181     that are all called by the same SQL role.
1182    </para>
1183   </note>
1184
1185  </sect1>
1186
1187  <sect1 id="plperl-triggers">
1188   <title>PL/Perl Triggers</title>
1189
1190   <para>
1191    PL/Perl can be used to write trigger functions.  In a trigger function,
1192    the hash reference <varname>$_TD</varname> contains information about the
1193    current trigger event. <varname>$_TD</varname> is a global variable,
1194    which gets a separate local value for each invocation of the trigger.
1195    The fields of the <varname>$_TD</varname> hash reference are:
1196
1197    <variablelist>
1198     <varlistentry>
1199      <term><literal>$_TD-&gt;{new}{foo}</literal></term>
1200      <listitem>
1201       <para>
1202        <literal>NEW</literal> value of column <literal>foo</literal>
1203       </para>
1204      </listitem>
1205     </varlistentry>
1206
1207     <varlistentry>
1208      <term><literal>$_TD-&gt;{old}{foo}</literal></term>
1209      <listitem>
1210       <para>
1211        <literal>OLD</literal> value of column <literal>foo</literal>
1212       </para>
1213      </listitem>
1214     </varlistentry>
1215
1216     <varlistentry>
1217      <term><literal>$_TD-&gt;{name}</literal></term>
1218      <listitem>
1219       <para>
1220        Name of the trigger being called
1221       </para>
1222      </listitem>
1223     </varlistentry>
1224
1225     <varlistentry>
1226      <term><literal>$_TD-&gt;{event}</literal></term>
1227      <listitem>
1228       <para>
1229        Trigger event: <literal>INSERT</literal>, <literal>UPDATE</literal>,
1230        <literal>DELETE</literal>, <literal>TRUNCATE</literal>, or <literal>UNKNOWN</literal>
1231       </para>
1232      </listitem>
1233     </varlistentry>
1234
1235     <varlistentry>
1236      <term><literal>$_TD-&gt;{when}</literal></term>
1237      <listitem>
1238       <para>
1239        When the trigger was called: <literal>BEFORE</literal>,
1240        <literal>AFTER</literal>, <literal>INSTEAD OF</literal>, or
1241        <literal>UNKNOWN</literal>
1242       </para>
1243      </listitem>
1244     </varlistentry>
1245
1246     <varlistentry>
1247      <term><literal>$_TD-&gt;{level}</literal></term>
1248      <listitem>
1249       <para>
1250        The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
1251       </para>
1252      </listitem>
1253     </varlistentry>
1254
1255     <varlistentry>
1256      <term><literal>$_TD-&gt;{relid}</literal></term>
1257      <listitem>
1258       <para>
1259        OID of the table on which the trigger fired
1260       </para>
1261      </listitem>
1262     </varlistentry>
1263
1264     <varlistentry>
1265      <term><literal>$_TD-&gt;{table_name}</literal></term>
1266      <listitem>
1267       <para>
1268        Name of the table on which the trigger fired
1269       </para>
1270      </listitem>
1271     </varlistentry>
1272
1273     <varlistentry>
1274      <term><literal>$_TD-&gt;{relname}</literal></term>
1275      <listitem>
1276       <para>
1277        Name of the table on which the trigger fired. This has been deprecated,
1278        and could be removed in a future release.
1279        Please use $_TD-&gt;{table_name} instead.
1280       </para>
1281      </listitem>
1282     </varlistentry>
1283
1284     <varlistentry>
1285      <term><literal>$_TD-&gt;{table_schema}</literal></term>
1286      <listitem>
1287       <para>
1288        Name of the schema in which the table on which the trigger fired, is
1289       </para>
1290      </listitem>
1291     </varlistentry>
1292
1293     <varlistentry>
1294      <term><literal>$_TD-&gt;{argc}</literal></term>
1295      <listitem>
1296       <para>
1297        Number of arguments of the trigger function
1298       </para>
1299      </listitem>
1300     </varlistentry>
1301
1302     <varlistentry>
1303      <term><literal>@{$_TD-&gt;{args}}</literal></term>
1304      <listitem>
1305       <para>
1306        Arguments of the trigger function.  Does not exist if <literal>$_TD-&gt;{argc}</literal> is 0.
1307       </para>
1308      </listitem>
1309     </varlistentry>
1310
1311    </variablelist>
1312   </para>
1313
1314   <para>
1315    Row-level triggers can return one of the following:
1316
1317    <variablelist>
1318     <varlistentry>
1319      <term><literal>return;</literal></term>
1320      <listitem>
1321       <para>
1322        Execute the operation
1323       </para>
1324      </listitem>
1325     </varlistentry>
1326
1327     <varlistentry>
1328      <term><literal>"SKIP"</literal></term>
1329      <listitem>
1330       <para>
1331        Don't execute the operation
1332       </para>
1333      </listitem>
1334     </varlistentry>
1335
1336     <varlistentry>
1337      <term><literal>"MODIFY"</literal></term>
1338      <listitem>
1339       <para>
1340        Indicates that the <literal>NEW</literal> row was modified by
1341        the trigger function
1342       </para>
1343      </listitem>
1344     </varlistentry>
1345    </variablelist>
1346   </para>
1347
1348   <para>
1349    Here is an example of a trigger function, illustrating some of the
1350    above:
1351 <programlisting>
1352 CREATE TABLE test (
1353     i int,
1354     v varchar
1355 );
1356
1357 CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
1358     if (($_TD-&gt;{new}{i} &gt;= 100) || ($_TD-&gt;{new}{i} &lt;= 0)) {
1359         return "SKIP";    # skip INSERT/UPDATE command
1360     } elsif ($_TD-&gt;{new}{v} ne "immortal") {
1361         $_TD-&gt;{new}{v} .= "(modified by trigger)";
1362         return "MODIFY";  # modify row and execute INSERT/UPDATE command
1363     } else {
1364         return;           # execute INSERT/UPDATE command
1365     }
1366 $$ LANGUAGE plperl;
1367
1368 CREATE TRIGGER test_valid_id_trig
1369     BEFORE INSERT OR UPDATE ON test
1370     FOR EACH ROW EXECUTE FUNCTION valid_id();
1371 </programlisting>
1372   </para>
1373  </sect1>
1374
1375  <sect1 id="plperl-event-triggers">
1376   <title>PL/Perl Event Triggers</title>
1377
1378   <para>
1379    PL/Perl can be used to write event trigger functions.  In an event trigger
1380    function, the hash reference <varname>$_TD</varname> contains information
1381    about the current trigger event.  <varname>$_TD</varname> is a global variable,
1382    which gets a separate local value for each invocation of the trigger.  The
1383    fields of the <varname>$_TD</varname> hash reference are:
1384
1385    <variablelist>
1386     <varlistentry>
1387      <term><literal>$_TD-&gt;{event}</literal></term>
1388      <listitem>
1389       <para>
1390        The name of the event the trigger is fired for.
1391       </para>
1392      </listitem>
1393     </varlistentry>
1394
1395     <varlistentry>
1396      <term><literal>$_TD-&gt;{tag}</literal></term>
1397      <listitem>
1398       <para>
1399        The command tag for which the trigger is fired.
1400       </para>
1401      </listitem>
1402     </varlistentry>
1403    </variablelist>
1404   </para>
1405
1406   <para>
1407    The return value of the trigger function is ignored.
1408   </para>
1409
1410   <para>
1411    Here is an example of an event trigger function, illustrating some of the
1412    above:
1413 <programlisting>
1414 CREATE OR REPLACE FUNCTION perlsnitch() RETURNS event_trigger AS $$
1415   elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");
1416 $$ LANGUAGE plperl;
1417
1418 CREATE EVENT TRIGGER perl_a_snitch
1419     ON ddl_command_start
1420     EXECUTE FUNCTION perlsnitch();
1421 </programlisting>
1422   </para>
1423  </sect1>
1424
1425  <sect1 id="plperl-under-the-hood">
1426   <title>PL/Perl Under the Hood</title>
1427
1428  <sect2 id="plperl-config">
1429   <title>Configuration</title>
1430
1431   <para>
1432   This section lists configuration parameters that affect <application>PL/Perl</application>.
1433   </para>
1434
1435   <variablelist>
1436
1437      <varlistentry id="guc-plperl-on-init" xreflabel="plperl.on_init">
1438       <term>
1439        <varname>plperl.on_init</varname> (<type>string</type>)
1440       <indexterm>
1441        <primary><varname>plperl.on_init</varname> configuration parameter</primary>
1442       </indexterm>
1443       </term>
1444       <listitem>
1445        <para>
1446         Specifies Perl code to be executed when a Perl interpreter is first
1447         initialized, before it is specialized for use by <literal>plperl</literal> or
1448         <literal>plperlu</literal>.
1449         The SPI functions are not available when this code is executed.
1450         If the code fails with an error it will abort the initialization of
1451         the interpreter and propagate out to the calling query, causing the
1452         current transaction or subtransaction to be aborted.
1453        </para>
1454        <para>
1455        The Perl code is limited to a single string. Longer code can be placed
1456        into a module and loaded by the <literal>on_init</literal> string.
1457        Examples:
1458 <programlisting>
1459 plperl.on_init = 'require "plperlinit.pl"'
1460 plperl.on_init = 'use lib "/my/app"; use MyApp::PgInit;'
1461 </programlisting>
1462        </para>
1463        <para>
1464        Any modules loaded by <literal>plperl.on_init</literal>, either directly or
1465        indirectly, will be available for use by <literal>plperl</literal>.  This may
1466        create a security risk. To see what modules have been loaded you can use:
1467 <programlisting>
1468 DO 'elog(WARNING, join ", ", sort keys %INC)' LANGUAGE plperl;
1469 </programlisting>
1470        </para>
1471        <para>
1472         Initialization will happen in the postmaster if the <literal>plperl</literal> library is
1473         included in <xref linkend="guc-shared-preload-libraries"/>, in which
1474         case extra consideration should be given to the risk of destabilizing
1475         the postmaster.  The principal reason for making use of this feature
1476         is that Perl modules loaded by <literal>plperl.on_init</literal> need be
1477         loaded only at postmaster start, and will be instantly available
1478         without loading overhead in individual database sessions.  However,
1479         keep in mind that the overhead is avoided only for the first Perl
1480         interpreter used by a database session &mdash; either PL/PerlU, or
1481         PL/Perl for the first SQL role that calls a PL/Perl function.  Any
1482         additional Perl interpreters created in a database session will have
1483         to execute <literal>plperl.on_init</literal> afresh.  Also, on Windows there
1484         will be no savings whatsoever from preloading, since the Perl
1485         interpreter created in the postmaster process does not propagate to
1486         child processes.
1487        </para>
1488        <para>
1489        This parameter can only be set in the <filename>postgresql.conf</filename> file or on the server command line.
1490        </para>
1491       </listitem>
1492      </varlistentry>
1493
1494      <varlistentry id="guc-plperl-on-plperl-init" xreflabel="plperl.on_plperl_init">
1495       <term>
1496        <varname>plperl.on_plperl_init</varname> (<type>string</type>)
1497        <indexterm>
1498         <primary><varname>plperl.on_plperl_init</varname> configuration parameter</primary>
1499        </indexterm>
1500       </term>
1501       <term>
1502        <varname>plperl.on_plperlu_init</varname> (<type>string</type>)
1503        <indexterm>
1504         <primary><varname>plperl.on_plperlu_init</varname> configuration parameter</primary>
1505        </indexterm>
1506       </term>
1507       <listitem>
1508        <para>
1509         These parameters specify Perl code to be executed when a Perl
1510         interpreter is specialized for <literal>plperl</literal> or
1511         <literal>plperlu</literal> respectively.  This will happen when a PL/Perl or
1512         PL/PerlU function is first executed in a database session, or when
1513         an additional interpreter has to be created because the other language
1514         is called or a PL/Perl function is called by a new SQL role.  This
1515         follows any initialization done by <literal>plperl.on_init</literal>.
1516         The SPI functions are not available when this code is executed.
1517         The Perl code in <literal>plperl.on_plperl_init</literal> is executed after
1518         <quote>locking down</quote> the interpreter, and thus it can only perform
1519         trusted operations.
1520        </para>
1521        <para>
1522         If the code fails with an error it will abort the initialization and
1523         propagate out to the calling query, causing the current transaction or
1524         subtransaction to be aborted.  Any actions already done within Perl
1525         won't be undone; however, that interpreter won't be used again.
1526         If the language is used again the initialization will be attempted
1527         again within a fresh Perl interpreter.
1528        </para>
1529        <para>
1530         Only superusers can change these settings.  Although these settings
1531         can be changed within a session, such changes will not affect Perl
1532         interpreters that have already been used to execute functions.
1533        </para>
1534       </listitem>
1535      </varlistentry>
1536
1537      <varlistentry id="guc-plperl-use-strict" xreflabel="plperl.use_strict">
1538       <term>
1539        <varname>plperl.use_strict</varname> (<type>boolean</type>)
1540        <indexterm>
1541         <primary><varname>plperl.use_strict</varname> configuration parameter</primary>
1542        </indexterm>
1543       </term>
1544       <listitem>
1545        <para>
1546         When set true subsequent compilations of PL/Perl functions will have
1547         the <literal>strict</literal> pragma enabled.  This parameter does not affect
1548         functions already compiled in the current session.
1549        </para>
1550       </listitem>
1551      </varlistentry>
1552
1553   </variablelist>
1554 </sect2>
1555
1556  <sect2 id="plperl-missing">
1557   <title>Limitations and Missing Features</title>
1558
1559   <para>
1560    The following features are currently missing from PL/Perl, but they
1561    would make welcome contributions.
1562
1563    <itemizedlist>
1564     <listitem>
1565      <para>
1566       PL/Perl functions cannot call each other directly.
1567      </para>
1568     </listitem>
1569
1570     <listitem>
1571      <para>
1572       SPI is not yet fully implemented.
1573      </para>
1574     </listitem>
1575
1576     <listitem>
1577      <para>
1578       If you are fetching very large data sets using
1579       <literal>spi_exec_query</literal>, you should be aware that
1580       these will all go into memory.  You can avoid this by using
1581       <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
1582       illustrated earlier.
1583      </para>
1584      <para>
1585         A similar problem occurs if a set-returning function passes a
1586         large set of rows back to PostgreSQL via <literal>return</literal>. You
1587         can avoid this problem too by instead using
1588         <literal>return_next</literal> for each row returned, as shown
1589         previously.
1590      </para>
1591     </listitem>
1592
1593      <listitem>
1594       <para>
1595         When a session ends normally, not due to a fatal error, any
1596         <literal>END</literal> blocks that have been defined are executed.
1597         Currently no other actions are performed. Specifically,
1598         file handles are not automatically flushed and objects are
1599         not automatically destroyed.
1600       </para>
1601      </listitem>
1602    </itemizedlist>
1603   </para>
1604  </sect2>
1605
1606  </sect1>
1607
1608 </chapter>