-<!--
-$PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.40 2005/05/20 01:52:24 neilc Exp $
--->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plperl.sgml,v 2.80 2010/02/12 19:35:25 adunstan Exp $ -->
<chapter id="plperl">
<title>PL/Perl - Perl Procedural Language</title>
<para>
PL/Perl is a loadable procedural language that enables you to write
- <productname>PostgreSQL</productname> functions in the
- <ulink url="http://www.perl.com">Perl programming language</ulink>.
+ <productname>PostgreSQL</productname> functions in the
+ <ulink url="http://www.perl.org">Perl programming language</ulink>.
+ </para>
+
+ <para>
+ The main advantage to using PL/Perl is that this allows use,
+ within stored functions, of the manyfold <quote>string
+ munging</quote> operators and functions available for Perl. Parsing
+ complex strings might be easier using Perl than it is with the
+ string functions and control structures provided in PL/pgSQL.
</para>
<para>
To create a function in the PL/Perl language, use the standard
<xref linkend="sql-createfunction" endterm="sql-createfunction-title">
syntax:
+
<programlisting>
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
# PL/Perl function body
$$ LANGUAGE plperl;
</programlisting>
- The body of the function is ordinary Perl code.
+
+ The body of the function is ordinary Perl code. In fact, the PL/Perl
+ glue code wraps it inside a Perl subroutine. A PL/Perl function is
+ called in a scalar context, so it can't return a list. You can return
+ non-scalar values (arrays, records, and sets) by returning a reference,
+ as discussed below.
</para>
+ <para>
+ PL/Perl also supports anonymous code blocks called with the
+ <xref linkend="sql-do" endterm="sql-do-title"> statement:
+
+<programlisting>
+DO $$
+ # PL/Perl code
+$$ LANGUAGE plperl;
+</programlisting>
+
+ An anonymous code block receives no arguments, and whatever value it
+ might return is discarded. Otherwise it behaves just like a function.
+ </para>
+
+ <note>
<para>
- The syntax of the <command>CREATE FUNCTION</command> command requires
- the function body to be written as a string constant. It is usually
- most convenient to use dollar quoting (see <xref
- linkend="sql-syntax-dollar-quoting">) for the string constant.
- If you choose to use regular single-quoted string constant syntax,
- you must escape single quote marks (<literal>'</>) and backslashes
- (<literal>\</>) used in the body of the function, typically by
- doubling them (see <xref linkend="sql-syntax-strings">).
+ The use of named nested subroutines is dangerous in Perl, especially if
+ they refer to lexical variables in the enclosing scope. Because a PL/Perl
+ function is wrapped in a subroutine, any named subroutine you create will
+ be nested. In general, it is far safer to create anonymous subroutines
+ which you call via a coderef. See <literal>Variable "%s" will not stay shared</literal>
+ and <literal>Variable "%s" is not available</literal> in the
+ <citerefentry><refentrytitle>perldiag</></citerefentry> man page for more
+ details.
</para>
+ </note>
+
+ <para>
+ The syntax of the <command>CREATE FUNCTION</command> command requires
+ the function body to be written as a string constant. It is usually
+ most convenient to use dollar quoting (see <xref
+ linkend="sql-syntax-dollar-quoting">) for the string constant.
+ If you choose to use escape string syntax <literal>E''</>,
+ you must double the single quote marks (<literal>'</>) and backslashes
+ (<literal>\</>) used in the body of the function
+ (see <xref linkend="sql-syntax-strings">).
+ </para>
<para>
Arguments and results are handled as in any other Perl subroutine:
<programlisting>
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
- my ($a,$b) = @_;
- if (! defined $a) {
- if (! defined $b) { return undef; }
- return $b;
+ my ($x, $y) = @_;
+ if (not defined $x) {
+ return undef if not defined $y;
+ return $y;
}
- if (! defined $b) { return $a; }
- if ($a > $b) { return $a; }
- return $b;
+ return $x if not defined $y;
+ return $x if $x > $y;
+ return $y;
$$ LANGUAGE plperl;
</programlisting>
As shown above, to return an SQL null value from a PL/Perl
</para>
<para>
+ Anything in a function argument that is not a reference is
+ a string, which is in the standard <productname>PostgreSQL</productname>
+ external text representation for the relevant data type. In the case of
+ ordinary numeric or text types, Perl will just do the right thing and
+ the programmer will normally not have to worry about it. However, in
+ other cases the argument will need to be converted into a form that is
+ more usable in Perl. For example, the <function>decode_bytea</function>
+ function can be used to convert an argument of
+ type <type>bytea</> into unescaped binary.
+ </para>
+
+ <para>
+ Similarly, values passed back to <productname>PostgreSQL</productname>
+ must be in the external text representation format. For example, the
+ <function>encode_bytea</function> function can be used to
+ to escape binary data for a return value of type <type>bytea</>.
+ </para>
+
+ <para>
+ Perl can return <productname>PostgreSQL</productname> arrays as
+ references to Perl arrays. Here is an example:
+
+<programlisting>
+CREATE OR REPLACE function returns_array()
+RETURNS text[][] AS $$
+ return [['a"b','c,d'],['e\\f','g']];
+$$ LANGUAGE plperl;
+
+select returns_array();
+</programlisting>
+ </para>
+
+ <para>
Composite-type arguments are passed to the function as references
to hashes. The keys of the hash are the attribute names of the
composite type. Here is an example:
<para>
A PL/Perl function can return a composite-type result using the same
approach: return a reference to a hash that has the required attributes.
- For example,
+ For example:
<programlisting>
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
</programlisting>
Any columns in the declared result data type that are not present in the
- hash will be returned as NULLs.
+ hash will be returned as null values.
</para>
<para>
- PL/Perl functions can also return sets of either scalar or composite
- types. To do this, return a reference to an array that contains
- either scalars or references to hashes, respectively. Here are
- some simple examples:
+ PL/Perl functions can also return sets of either scalar or
+ composite types. Usually you'll want to return rows one at a
+ time, both to speed up startup time and to keep from queueing up
+ the entire result set in memory. You can do this with
+ <function>return_next</function> as illustrated below. Note that
+ after the last <function>return_next</function>, you must put
+ either <literal>return</literal> or (better) <literal>return
+ undef</literal>.
<programlisting>
-CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
-return [0..$_[0]];
+CREATE OR REPLACE FUNCTION perl_set_int(int)
+RETURNS SETOF INTEGER AS $$
+ foreach (0..$_[0]) {
+ return_next($_);
+ }
+ return undef;
$$ LANGUAGE plperl;
SELECT * FROM perl_set_int(5);
+CREATE OR REPLACE FUNCTION perl_set()
+RETURNS SETOF testrowperl AS $$
+ return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
+ return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
+ return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
+ return undef;
+$$ LANGUAGE plperl;
+</programlisting>
+
+ For small result sets, you can return a reference to an array that
+ contains either scalars, references to arrays, or references to
+ hashes for simple types, array types, and composite types,
+ respectively. Here are some simple examples of returning the entire
+ result set as an array reference:
+
+<programlisting>
+CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
+ return [0..$_[0]];
+$$ LANGUAGE plperl;
+
+SELECT * FROM perl_set_int(5);
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
return [
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
];
-$$ LANGUAGE plperl;
+$$ LANGUAGE plperl;
SELECT * FROM perl_set();
</programlisting>
+ </para>
- Note that when you do this, Perl will have to build the entire array in
- memory; therefore the technique does not scale to very large result sets.
+ <para>
+ If you wish to use the <literal>strict</> pragma with your code you
+ have a few options. For temporary global use you can <command>SET</>
+ <literal>plperl.use_strict</literal> to true.
+ This will affect subsequent compilations of <application>PL/Perl</>
+ functions, but not functions already compiled in the current session.
+ For permanent global use you can set <literal>plperl.use_strict</literal>
+ to true in the <filename>postgresql.conf</filename> file.
+ </para>
+
+ <para>
+ For permanent use in specific functions you can simply put:
+<programlisting>
+use strict;
+</programlisting>
+ at the top of the function body.
+ </para>
+
+ <para>
+ The <literal>feature</> pragma is also available to <function>use</> if your Perl is version 5.10.0 or higher.
</para>
- <para>
- <application>PL/Perl</> does not currently have full support for
- domain types: it treats a domain the same as the underlying scalar
- type. This means that constraints associated with the domain will
- not be enforced. This is not an issue for function arguments, but
- it is a hazard if you declare a <application>PL/Perl</> function
- as returning a domain type.
- </para>
</sect1>
- <sect1 id="plperl-database">
- <title>Database Access from PL/Perl</title>
+ <sect1 id="plperl-data">
+ <title>Data Values in PL/Perl</title>
<para>
- Access to the database itself from your Perl function can be done
- via the function <function>spi_exec_query</function> described
- below, or via an experimental module
- <ulink url="http://www.cpan.org/modules/by-module/DBD/APILOS/">
- <literal>DBD::PgSPI</literal></ulink>
- (also available at <ulink url="http://www.cpan.org/SITES.html">
- <acronym>CPAN mirror sites</></ulink>). This module makes available a
- <acronym>DBI</>-compliant database-handle named
- <varname>$pg_dbh</varname> that can be used to perform queries with
- normal <acronym>DBI</>
- syntax.<indexterm><primary>DBI</></indexterm>
+ 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 <function>return</function> and <function>return_next</function>
+ commands will accept any string that is acceptable input format
+ for the function's declared return type.
</para>
+ </sect1>
+
+ <sect1 id="plperl-builtins">
+ <title>Built-in Functions</title>
+
+ <sect2 id="plperl-database">
+ <title>Database Access from PL/Perl</title>
<para>
- PL/Perl itself presently provides two additional Perl commands:
+ Access to the database itself from your Perl function can be done
+ via the following functions:
+ </para>
<variablelist>
<varlistentry>
<primary>spi_exec_query</primary>
<secondary>in PL/Perl</secondary>
</indexterm>
+ <indexterm>
+ <primary>spi_query</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_fetchrow</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_prepare</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_exec_prepared</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_query_prepared</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_cursor_close</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>spi_freeplan</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
<term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
- <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
<listitem>
<para>
- Executes an SQL command. Here is an example of a query
- (<command>SELECT</command> command) with the optional maximum
- number of rows:
+ <literal>spi_exec_query</literal> executes an SQL command and
+returns the entire row set as a reference to an array of hash
+references. <emphasis>You should only use this command when you know
+that the result set will be relatively small.</emphasis> Here is an
+example of a query (<command>SELECT</command> command) with the
+optional maximum number of rows:
+
<programlisting>
$rv = spi_exec_query('SELECT * FROM my_table', 5);
</programlisting>
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');
-CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
- my $res = [];
+CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
my $rv = spi_exec_query('select i, v from test;');
my $status = $rv->{status};
my $nrows = $rv->{processed};
my $row = $rv->{rows}[$rn];
$row->{i} += 200 if defined($row->{i});
$row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
- push @$res, $row;
+ return_next($row);
}
- return $res;
+ return undef;
$$ LANGUAGE plperl;
SELECT * FROM test_munge();
</programlisting>
- </para>
- </listitem>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term>
+ <term><literal><function>spi_fetchrow</>(<replaceable>cursor</replaceable>)</literal></term>
+ <term><literal><function>spi_cursor_close</>(<replaceable>cursor</replaceable>)</literal></term>
+
+ <listitem>
+ <para>
+ <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
+ work together as a pair for row sets which might be large, or for cases
+ where you wish to return rows as they arrive.
+ <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
+ <literal>spi_query</literal>. The following example illustrates how
+ you use them together:
+
+<programlisting>
+CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
+
+CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
+ use Digest::MD5 qw(md5_hex);
+ my $file = '/usr/share/dict/words';
+ my $t = localtime;
+ elog(NOTICE, "opening file $file at $t" );
+ open my $fh, '<', $file # ooh, it's a file access!
+ or elog(ERROR, "cannot open $file for reading: $!");
+ my @words = <$fh>;
+ close $fh;
+ $t = localtime;
+ elog(NOTICE, "closed file $file at $t");
+ chomp(@words);
+ my $row;
+ my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
+ while (defined ($row = spi_fetchrow($sth))) {
+ return_next({
+ the_num => $row->{a},
+ the_text => md5_hex($words[rand @words])
+ });
+ }
+ return;
+$$ LANGUAGE plperlu;
+
+SELECT * from lotsa_md5(500);
+</programlisting>
+ </para>
+
+ <para>
+ Normally, <function>spi_fetchrow</> should be repeated until it
+ returns <literal>undef</literal>, indicating that there are no more
+ rows to read. The cursor returned by <literal>spi_query</literal>
+ is automatically freed when
+ <function>spi_fetchrow</> returns <literal>undef</literal>.
+ If you do not wish to read all the rows, instead call
+ <function>spi_cursor_close</> to free the cursor.
+ Failure to do so will result in memory leaks.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal><function>spi_prepare</>(<replaceable>command</replaceable>, <replaceable>argument types</replaceable>)</literal></term>
+ <term><literal><function>spi_query_prepared</>(<replaceable>plan</replaceable>, <replaceable>arguments</replaceable>)</literal></term>
+ <term><literal><function>spi_exec_prepared</>(<replaceable>plan</replaceable> [, <replaceable>attributes</replaceable>], <replaceable>arguments</replaceable>)</literal></term>
+ <term><literal><function>spi_freeplan</>(<replaceable>plan</replaceable>)</literal></term>
+
+ <listitem>
+ <para>
+ <literal>spi_prepare</literal>, <literal>spi_query_prepared</literal>, <literal>spi_exec_prepared</literal>,
+ and <literal>spi_freeplan</literal> implement the same functionality but for prepared queries.
+ <literal>spi_prepare</literal> accepts a query string with numbered argument placeholders ($1, $2, etc)
+ and a string list of argument types:
+<programlisting>
+$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');
+</programlisting>
+ Once a query plan is prepared by a call to <literal>spi_prepare</literal>, the plan can be used instead
+ of the string query, either in <literal>spi_exec_prepared</literal>, where the result is the same as returned
+ by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepared</literal> which returns a cursor
+ exactly as <literal>spi_query</literal> does, which can be later passed to <literal>spi_fetchrow</literal>.
+ The optional second parameter to <literal>spi_exec_prepared</literal> is a hash reference of attributes;
+ the only attribute currently supported is <literal>limit</literal>, which sets the maximum number of rows returned by a query.
+ </para>
+
+ <para>
+ The advantage of prepared queries is that is it possible to use one prepared plan for more
+ than one query execution. After the plan is not needed anymore, it can be freed with
+ <literal>spi_freeplan</literal>:
+ </para>
+
+ <para>
+ <programlisting>
+CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
+ $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
+ return spi_exec_prepared(
+ $_SHARED{my_plan},
+ $_[0]
+ )->{rows}->[0]->{now};
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
+ spi_freeplan( $_SHARED{my_plan});
+ undef $_SHARED{my_plan};
+$$ LANGUAGE plperl;
+
+SELECT init();
+SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
+SELECT done();
+
+ add_time | add_time | add_time
+------------+------------+------------
+ 2005-12-10 | 2005-12-11 | 2005-12-12
+ </programlisting>
+ </para>
+
+ <para>
+ Note that the parameter subscript in <literal>spi_prepare</literal> is defined via
+ $1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily
+ lead to hard-to-catch bugs.
+ </para>
+
+ <para>
+ Another example illustrates usage of an optional parameter in <literal>spi_exec_prepared</literal>:
+ </para>
+
+ <para>
+ <programlisting>
+CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address FROM generate_series(1,3) AS id;
+
+CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
+ $_SHARED{plan} = spi_prepare('SELECT * FROM hosts WHERE address << $1', 'inet');
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
+ return spi_exec_prepared(
+ $_SHARED{plan},
+ {limit => 2},
+ $_[0]
+ )->{rows};
+$$ LANGUAGE plperl;
+
+CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
+ spi_freeplan($_SHARED{plan});
+ undef $_SHARED{plan};
+$$ LANGUAGE plperl;
+
+SELECT init_hosts_query();
+SELECT query_hosts('192.168.1.0/30');
+SELECT release_hosts_query();
+
+ query_hosts
+-----------------
+ (1,192.168.1.1)
+ (2,192.168.1.2)
+(2 rows)
+ </programlisting>
+ </para>
+ </listitem>
</varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2 id="plperl-utility-functions">
+ <title>Utility functions in PL/Perl</title>
+ <variablelist>
<varlistentry>
<indexterm>
<primary>elog</primary>
</para>
</listitem>
</varlistentry>
- </variablelist>
- </para>
- </sect1>
- <sect1 id="plperl-data">
- <title>Data Values in PL/Perl</title>
+ <varlistentry>
+ <indexterm>
+ <primary>quote_literal</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
- <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>
+ <term><literal><function>quote_literal</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Return the given string suitably quoted to be used as a string literal in an SQL
+ statement string. Embedded single-quotes and backslashes are properly doubled.
+ Note that <function>quote_literal</> returns undef on undef input; if the argument
+ might be undef, <function>quote_nullable</> is often more suitable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>quote_nullable</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>quote_nullable</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Return the given string suitably quoted to be used as a string literal in an SQL
+ statement string; or, if the argument is undef, return the unquoted string "NULL".
+ Embedded single-quotes and backslashes are properly doubled.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>quote_ident</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>quote_ident</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Return the given string suitably quoted to be used as an identifier in
+ an SQL statement string. Quotes are added only if necessary (i.e., if
+ the string contains non-identifier characters or would be case-folded).
+ Embedded quotes are properly doubled.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>decode_bytea</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>decode_bytea</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Return the unescaped binary data represented by the contents of the given string,
+ which should be bytea encoded.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>encode_bytea</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>encode_bytea</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Return the bytea encoded form of the binary data contents of the given string.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>encode_array_literal</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>encode_array_literal</>(<replaceable>array</replaceable>)</literal></term>
+ <term><literal><function>encode_array_literal</>(<replaceable>array</replaceable>, <replaceable>delimiter</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Returns the contents of the referenced array as a string in array literal format
+ (see <xref linkend="arrays-input">).
+ Returns the argument value unaltered if it's not a reference to an array.
+ The delimiter used between elements of the array literal defaults to "<literal>, </literal>"
+ if a delimiter is not specified or is undef.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>encode_array_constructor</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>encode_array_constructor</>(<replaceable>array</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Returns the contents of the referenced array as a string in array constructor format
+ (see <xref linkend="sql-syntax-array-constructors">).
+ Individual values are quoted using <function>quote_nullable</function>.
+ Returns the argument value, quoted using <function>quote_nullable</function>,
+ if it's not a reference to an array.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <indexterm>
+ <primary>looks_like_number</primary>
+ <secondary>in PL/Perl</secondary>
+ </indexterm>
+
+ <term><literal><function>looks_like_number</>(<replaceable>string</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ Returns a true value if the content of the given string looks like a
+ number, according to Perl, returns false otherwise.
+ Returns undef if the argument is undef. Leading and trailing space is
+ ignored. <literal>Inf</> and <literal>Infinity</> are regarded as numbers.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
</sect1>
<sect1 id="plperl-global">
if ($_SHARED{$_[0]} = $_[1]) {
return 'ok';
} else {
- return "can't set shared variable $_[0] to $_[1]";
+ return "cannot set shared variable $_[0] to $_[1]";
}
$$ LANGUAGE plperl;
return $_SHARED{$_[0]};
$$ LANGUAGE plperl;
-SELECT set_var('sample', 'Hello, PL/Perl! How's tricks?');
+SELECT set_var('sample', 'Hello, PL/Perl! How''s tricks?');
SELECT get_var('sample');
</programlisting>
</para>
<literal>return $_SHARED{myquote}->($_[0]);</literal>
at the expense of readability.)
</para>
+
+ <para>
+ The <varname>%_SHARED</varname> variable and other global state within
+ the language is public data, available to all PL/Perl functions within a
+ session. Use with care, especially in situations that involve use of
+ multiple roles or <literal>SECURITY DEFINER</> functions.
+ </para>
</sect1>
<sect1 id="plperl-trusted">
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
+ as a C function can do. Thus, any unprivileged database user can
be permitted to use this language.
</para>
system operations are not allowed for security reasons:
<programlisting>
CREATE FUNCTION badfunc() RETURNS integer AS $$
- open(TEMP, ">/tmp/badfile");
- print TEMP "Gotcha!\n";
+ my $tmpfile = "/tmp/badfile";
+ open my $fh, '>', $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 the function will succeed, but executing it will not.
+ The creation of this function will fail as its use of a forbidden
+ operation will be caught by the validator.
</para>
<para>
If the above function was created by a superuser using the language
<literal>plperlu</>, execution would succeed.
</para>
+
+ <para>
+ In the same way, anonymous code blocks written in Perl can use
+ restricted operations if the language is specified as
+ <literal>plperlu</> rather than <literal>plperl</>, but the caller
+ must be a superuser.
+ </para>
+
+ <note>
+ <para>
+ For security reasons, to stop a leak of privileged operations from
+ <application>PL/PerlU</> to <application>PL/Perl</>, these two languages
+ have to run in separate instances of the Perl interpreter. If your
+ Perl installation has been appropriately compiled, this is not a problem.
+ However, not all installations are compiled with the requisite flags.
+ If <productname>PostgreSQL</> detects that this is the case then it will
+ not start a second interpreter, but instead create an error. In
+ consequence, in such an installation, you cannot use both
+ <application>PL/PerlU</> and <application>PL/Perl</> in the same backend
+ process. The remedy for this is to obtain a Perl installation configured
+ with the appropriate flags, namely either <literal>usemultiplicity</>
+ or <literal>useithreads</>. <literal>usemultiplicity</> is preferred
+ unless you actually need to use threads. For more details, see the
+ <citerefentry><refentrytitle>perlembed</></citerefentry> man page.
+ </para>
+ </note>
+
</sect1>
<sect1 id="plperl-triggers">
<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:
+ current trigger event. <varname>$_TD</> is a global variable,
+ which gets a separate local value for each invocation of the trigger.
+ The fields of the <varname>$_TD</varname> hash reference are:
<variablelist>
<varlistentry>
<term><literal>$_TD->{event}</literal></term>
<listitem>
<para>
- Trigger event: <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or <literal>UNKNOWN</>
+ Trigger event: <literal>INSERT</>, <literal>UPDATE</>,
+ <literal>DELETE</>, <literal>TRUNCATE</>, or <literal>UNKNOWN</>
</para>
</listitem>
</varlistentry>
</varlistentry>
<varlistentry>
- <term><literal>$_TD->{relname}</literal></term>
+ <term><literal>$_TD->{table_name}</literal></term>
<listitem>
<para>
Name of the table on which the trigger fired
</varlistentry>
<varlistentry>
+ <term><literal>$_TD->{relname}</literal></term>
+ <listitem>
+ <para>
+ Name of the table on which the trigger fired. This has been deprecated,
+ and could be removed in a future release.
+ Please use $_TD->{table_name} instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>$_TD->{table_schema}</literal></term>
+ <listitem>
+ <para>
+ Name of the schema in which the table on which the trigger fired, is
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>$_TD->{argc}</literal></term>
<listitem>
<para>
<term><literal>@{$_TD->{args}}</literal></term>
<listitem>
<para>
- Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.
+ Arguments of the trigger function. Does not exist if <literal>$_TD->{argc}</literal> is 0.
</para>
</listitem>
</varlistentry>
</para>
<para>
- Triggers can return one of the following:
+ Row-level triggers can return one of the following:
<variablelist>
<varlistentry>
<term><literal>return;</literal></term>
<listitem>
<para>
- Execute the statement
+ Execute the operation
</para>
</listitem>
</varlistentry>
<term><literal>"SKIP"</literal></term>
<listitem>
<para>
- Don't execute the statement
+ Don't execute the operation
</para>
</listitem>
</varlistentry>
</para>
</sect1>
- <sect1 id="plperl-missing">
+ <sect1 id="plperl-under-the-hood">
+ <title>PL/Perl Under the Hood</title>
+
+ <sect2 id="plperl-config">
+ <title>Configuration</title>
+
+ <para>
+ This section lists configuration parameters that affect <application>PL/Perl</>.
+ To set any of these parameters before <application>PL/Perl</> has been loaded,
+ it is necessary to have added <quote><literal>plperl</></> to the
+ <xref linkend="guc-custom-variable-classes"> list in
+ <filename>postgresql.conf</filename>.
+ </para>
+
+ <variablelist>
+
+ <varlistentry id="guc-plperl-on-init" xreflabel="plperl.on_init">
+ <term><varname>plperl.on_init</varname> (<type>string</type>)</term>
+ <indexterm>
+ <primary><varname>plperl.on_init</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies Perl code to be executed when a Perl interpreter is first initialized
+ and before it is specialized for use by <literal>plperl</> or <literal>plperlu</>.
+ The SPI functions are not available when this code is executed.
+ If the code fails with an error it will abort the initialization of the interpreter
+ and propagate out to the calling query, causing the current transaction
+ or subtransaction to be aborted.
+ </para>
+ <para>
+ The Perl code is limited to a single string. Longer code can be placed
+ into a module and loaded by the <literal>on_init</> string.
+ Examples:
+<programlisting>
+plplerl.on_init = '$ENV{NYTPROF}="start=no"; require Devel::NYTProf::PgPLPerl'
+plplerl.on_init = 'use lib "/my/app"; use MyApp::PgInit;'
+</programlisting>
+ </para>
+ <para>
+ Initialization will happen in the postmaster if the plperl library is included
+ in <literal>shared_preload_libraries</> (see <xref linkend="guc-shared-preload-libraries">),
+ in which case extra consideration should be given to the risk of destabilizing the postmaster.
+ </para>
+ <para>
+ This parameter can only be set in the postgresql.conf file or on the server command line.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-plperl-on-plperl-init" xreflabel="plperl.on_plperl_init">
+ <term><varname>plperl.on_plperl_init</varname> (<type>string</type>)</term>
+ <term><varname>plperl.on_plperlu_init</varname> (<type>string</type>)</term>
+ <indexterm>
+ <primary><varname>plperl.on_plperl_init</> configuration parameter</primary>
+ </indexterm>
+ <indexterm>
+ <primary><varname>plperl.on_plperlu_init</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ These parameters specify Perl code to be executed when the
+ <literal>plperl</>, or <literal>plperlu</> language is first used in a
+ session. Changes to these parameters after the corresponding language
+ has been used will have no effect.
+ The SPI functions are not available when this code is executed.
+ Only superusers can change these settings.
+ The Perl code in <literal>plperl.on_plperl_init</> can only perform trusted operations.
+ </para>
+ <para>
+ The effect of setting these parameters is very similar to executing a
+ <literal>DO</> command with the Perl code before any other use of the
+ language. The parameters are useful when you want to execute the Perl
+ code automatically on every connection, or when a connection is not
+ interactive. The parameters can be used by non-superusers by having a
+ superuser execute an <literal>ALTER USER ... SET ...</> command.
+ For example:
+<programlisting>
+ALTER USER joe SET plplerl.on_plperl_init = '$_SHARED{debug} = 1';
+</programlisting>
+ </para>
+ <para>
+ If the code fails with an error it will abort the initialization and
+ propagate out to the calling query, causing the current transaction or
+ subtransaction to be aborted. Any changes within Perl won't be undone.
+ If the language is used again the initialization will be repeated.
+ </para>
+ <para>
+ The difference between these two settings and the
+ <literal>plperl.on_init</> setting is that these can be used for
+ settings specific to the trusted or untrusted language variant, such
+ as setting values in the <varname>%_SHARED</> variable. By contrast,
+ <literal>plperl.on_init</> is more useful for doing things like
+ setting the library search path for <productname>Perl</> or
+ loading Perl modules that don't interact directly with
+ <productname>PostgreSQL</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-plperl-use-strict" xreflabel="plperl.use_strict">
+ <term><varname>plperl.use_strict</varname> (<type>boolean</type>)</term>
+ <indexterm>
+ <primary><varname>plperl.use_strict</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ When set true subsequent compilations of PL/Perl functions have the <literal>strict</> pragma enabled.
+ This parameter does not affect functions already compiled in the current session.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+</sect2>
+
+ <sect2 id="plperl-missing">
<title>Limitations and Missing Features</title>
<para>
<itemizedlist>
<listitem>
<para>
- PL/Perl functions cannot call each other directly (because they
- are anonymous subroutines inside Perl).
+ PL/Perl functions cannot call each other directly.
</para>
</listitem>
<listitem>
<para>
- In the current implementation, if you are fetching or returning
- very large data sets, you should be aware that these will all go
- into memory.
+ 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>
+
+ <listitem>
+ <para>
+ When a session ends normally, not due to a fatal error, any
+ <literal>END</> blocks that have been defined are executed.
+ Currently no other actions are performed. Specifically,
+ file handles are not automatically flushed and objects are
+ not automatically destroyed.
+ </para>
+ </listitem>
</itemizedlist>
</para>
+ </sect2>
+
</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:
--->
OSDN Git Service
