plperl.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

        return_next({
            the_num => $row->{a},
            the_text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);
</programlisting>
    </para>

     </listitem>
    </varlistentry>

    <varlistentry>
     <indexterm>
      <primary>elog</primary>
      <secondary>in PL/Perl</secondary>
     </indexterm>

     <term><literal><function>elog</>(<replaceable>level</replaceable>, <replaceable>msg</replaceable>)</literal></term>
     <listitem>
      <para>
       Emit a log or error message. Possible levels are
       <literal>DEBUG</>, <literal>LOG</>, <literal>INFO</>,
       <literal>NOTICE</>, <literal>WARNING</>, and <literal>ERROR</>.
       <literal>ERROR</>
        raises an error condition; if this is not trapped by the surrounding
        Perl code, the error propagates out to the calling query, causing
        the current transaction or subtransaction to be aborted.  This
        is effectively the same as the Perl <literal>die</> command.
        The other levels only generate messages of different
        priority levels.
        Whether messages of a particular priority are reported to the client,
        written to the server log, or both is controlled by the
        <xref linkend="guc-log-min-messages"> and
        <xref linkend="guc-client-min-messages"> configuration
        variables. See <xref linkend="runtime-config"> for more
        information.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
 </sect1>

 <sect1 id="plperl-data">
  <title>Data Values in PL/Perl</title>

  <para>
   The argument values supplied to a PL/Perl function's code are
   simply the input arguments converted to text form (just as if they
   had been displayed by a <command>SELECT</command> statement).
   Conversely, the <literal>return</> command will accept any string
   that is acceptable input format for the function's declared return
   type.  So, within the PL/Perl function,
   all values are just text strings.
  </para>
 </sect1>

 <sect1 id="plperl-global">
  <title>Global Values in PL/Perl</title>

  <para>
    You can use the global hash <varname>%_SHARED</varname> to store
    data, including code references, between function calls for the
    lifetime of the current session.
  </para>

  <para>
    Here is a simple example for shared data:
<programlisting>
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
    if ($_SHARED{$_[0]} = $_[1]) {
        return 'ok';
    } else {
        return "can't set shared variable $_[0] to $_[1]";
    }
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
    return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

SELECT set_var('sample', 'Hello, PL/Perl!  How's tricks?');
SELECT get_var('sample');
</programlisting>
  </para>

  <para>
   Here is a slightly more complicated example using a code reference:

<programlisting>
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
    $_SHARED{myquote} = sub {
        my $arg = shift;
        $arg =~ s/(['\\])/\\$1/g;
        return "'$arg'";
    };
$$ LANGUAGE plperl;

SELECT myfuncs(); /* initializes the function */

/* Set up a function that uses the quote function */

CREATE OR REPLACE FUNCTION use_quote(TEXT) RETURNS text AS $$
    my $text_to_quote = shift;
    my $qfunc = $_SHARED{myquote};
    return &amp;$qfunc($text_to_quote);
$$ LANGUAGE plperl;
</programlisting>

   (You could have replaced the above with the one-liner
   <literal>return $_SHARED{myquote}-&gt;($_[0]);</literal>
   at the expense of readability.)
  </para>
 </sect1>

 <sect1 id="plperl-trusted">
  <title>Trusted and Untrusted PL/Perl</title>

  <indexterm zone="plperl-trusted">
   <primary>trusted</primary>
   <secondary>PL/Perl</secondary>
  </indexterm>

  <para>
   Normally, PL/Perl is installed as a <quote>trusted</> programming
   language named <literal>plperl</>.  In this setup, certain Perl
   operations are disabled to preserve security.  In general, the
   operations that are restricted are those that interact with the
   environment. This includes file handle operations,
   <literal>require</literal>, and <literal>use</literal> (for
   external modules).  There is no way to access internals of the
   database server process or to gain OS-level access with the
   permissions of the server process,
   as a C function can do.  Thus, any unprivileged database user may
   be permitted to use this language.
  </para>

  <para>
   Here is an example of a function that will not work because file
   system operations are not allowed for security reasons:
<programlisting>
CREATE FUNCTION badfunc() RETURNS integer AS $$
    my $tmpfile = "/tmp/badfile";
    open my $fh, '&gt;', $tmpfile
        or elog(ERROR, qq{Could not open the file "$tmpfile": $!});
    print $fh "Testing writing to a file\n";
    close $fh or elog(ERROR, qq{Could not close the file "$tmpfile": $!});
    return 1;
$$ LANGUAGE plperl;
</programlisting>
	The creation of this function will fail as its use of a forbidden
	operation will be be caught by the validator.
  </para>

  <para>
   Sometimes it is desirable to write Perl functions that are not
   restricted.  For example, one might want a Perl function that sends
   mail.  To handle these cases, PL/Perl can also be installed as an
   <quote>untrusted</> language (usually called
   <application>PL/PerlU</application><indexterm><primary>PL/PerlU</></indexterm>).
   In this case the full Perl language is available.  If the
   <command>createlang</command> program is used to install the
   language, the language name <literal>plperlu</literal> will select
   the untrusted PL/Perl variant.
  </para>

  <para>
   The writer of a <application>PL/PerlU</> function must take care that the function
   cannot be used to do anything unwanted, since it will be able to do
   anything that could be done by a user logged in as the database
   administrator.  Note that the database system allows only database
   superusers to create functions in untrusted languages.
  </para>

  <para>
   If the above function was created by a superuser using the language
   <literal>plperlu</>, execution would succeed.
  </para>
 </sect1>

 <sect1 id="plperl-triggers">
  <title>PL/Perl Triggers</title>

  <para>
   PL/Perl can be used to write trigger functions.  In a trigger function,
   the hash reference <varname>$_TD</varname> contains information about the
   current trigger event.  The fields of the <varname>$_TD</varname> hash
   reference are:

   <variablelist>
    <varlistentry>
     <term><literal>$_TD-&gt;{new}{foo}</literal></term>
     <listitem>
      <para>
       <literal>NEW</literal> value of column <literal>foo</literal>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{old}{foo}</literal></term>
     <listitem>
      <para>
       <literal>OLD</literal> value of column <literal>foo</literal>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{name}</literal></term>
     <listitem>
      <para>
       Name of the trigger being called
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{event}</literal></term>
     <listitem>
      <para>
       Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{when}</literal></term>
     <listitem>
      <para>
       When the trigger was called: <literal>BEFORE</literal>, <literal>AFTER</literal>, or <literal>UNKNOWN</literal>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{level}</literal></term>
     <listitem>
      <para>
       The trigger level: <literal>ROW</literal>, <literal>STATEMENT</literal>, or <literal>UNKNOWN</literal>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{relid}</literal></term>
     <listitem>
      <para>
       OID of the table on which the trigger fired
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{relname}</literal></term>
     <listitem>
      <para>
       Name of the table on which the trigger fired
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>$_TD-&gt;{argc}</literal></term>
     <listitem>
      <para>
       Number of arguments of the trigger function
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>@{$_TD-&gt;{args}}</literal></term>
     <listitem>
      <para>
       Arguments of the trigger function.  Does not exist if <literal>$_TD-&gt;{argc}</literal> is 0.
      </para>
     </listitem>
    </varlistentry>

   </variablelist>
  </para>

  <para>
   Triggers can return one of the following:

   <variablelist>
    <varlistentry>
     <term><literal>return;</literal></term>
     <listitem>
      <para>
       Execute the statement
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>"SKIP"</literal></term>
     <listitem>
      <para>
       Don't execute the statement
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>"MODIFY"</literal></term>
     <listitem>
      <para>
       Indicates that the <literal>NEW</literal> row was modified by
       the trigger function
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>

  <para>
   Here is an example of a trigger function, illustrating some of the
   above:
<programlisting>
CREATE TABLE test (
    i int,
    v varchar
);

CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
    if (($_TD-&gt;{new}{i} &gt;= 100) || ($_TD-&gt;{new}{i} &lt;= 0)) {
        return "SKIP";    # skip INSERT/UPDATE command
    } elsif ($_TD-&gt;{new}{v} ne "immortal") {
        $_TD-&gt;{new}{v} .= "(modified by trigger)";
        return "MODIFY";  # modify row and execute INSERT/UPDATE command
    } else {
        return;           # execute INSERT/UPDATE command
    }
$$ LANGUAGE plperl;

CREATE TRIGGER test_valid_id_trig
    BEFORE INSERT OR UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE valid_id();
</programlisting>
  </para>
 </sect1>

 <sect1 id="plperl-missing">
  <title>Limitations and Missing Features</title>

  <para>
   The following features are currently missing from PL/Perl, but they
   would make welcome contributions.

   <itemizedlist>
    <listitem>
     <para>
      PL/Perl functions cannot call each other directly (because they
      are anonymous subroutines inside Perl).
     </para>
    </listitem>

    <listitem>
     <para>
      SPI is not yet fully implemented.
     </para>
    </listitem>

    <listitem>
     <para>
      If you are fetching very large data sets using
      <literal>spi_exec_query</literal>, you should be aware that
      these will all go into memory.  You can avoid this by using
      <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
      illustrated earlier.
     </para>
     <para>
        A similar problem occurs if a set-returning function passes a
        large set of rows back to PostgreSQL via <literal>return</literal>. You
        can avoid this problem too by instead using
        <literal>return_next</literal> for each row returned, as shown
        previously.
     </para>

    </listitem>
   </itemizedlist>
  </para>
 </sect1>

</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->