| 1 |
<!-- doc/src/sgml/plperl.sgml --> |
| 2 |
|
| 3 |
<chapter id="plperl"> |
| 4 |
<title>PL/Perl — Perl Procedural Language</title> |
| 5 |
|
| 6 |
<indexterm zone="plperl"> |
| 7 |
<primary>PL/Perl</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<indexterm zone="plperl"> |
| 11 |
<primary>Perl</primary> |
| 12 |
</indexterm> |
| 13 |
|
| 14 |
<para> |
| 15 |
PL/Perl is a loadable procedural language that enables you to write |
| 16 |
<productname>PostgreSQL</productname> functions 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] > $_[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 > $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 && $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"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->{basesalary} + $emp->{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 => 'hello', f1 => 1, f3 => '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 => $a * 3, b => $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 => 1, f2 => 'Hello', f3 => 'World' }); |
| 355 |
return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }); |
| 356 |
return_next({ f1 => 3, f2 => 'Hello', f3 => '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 => 1, f2 => 'Hello', f3 => 'World' }, |
| 377 |
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, |
| 378 |
{ f1 => 3, f2 => 'Hello', f3 => '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->{rows}[$i]->{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->{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->{status}; |
| 494 |
</programlisting> |
| 495 |
To get the number of rows affected, do: |
| 496 |
<programlisting> |
| 497 |
$nrows = $rv->{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->{status}; |
| 517 |
my $nrows = $rv->{processed}; |
| 518 |
foreach my $rn (0 .. $nrows - 1) { |
| 519 |
my $row = $rv->{rows}[$rn]; |
| 520 |
$row->{i} += 200 if defined($row->{i}); |
| 521 |
$row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{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, '<', $file # ooh, it's a file access! |
| 574 |
or elog(ERROR, "cannot open $file for reading: $!"); |
| 575 |
my @words = <$fh>; |
| 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 => $row->{a}, |
| 585 |
the_text => 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 > $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 << $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 => 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 &$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}->($_[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, '>', $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->{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->{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->{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->{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->{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->{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->{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->{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->{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->{table_name} instead. |
| 1280 |
</para> |
| 1281 |
</listitem> |
| 1282 |
</varlistentry> |
| 1283 |
|
| 1284 |
<varlistentry> |
| 1285 |
<term><literal>$_TD->{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->{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->{args}}</literal></term> |
| 1304 |
<listitem> |
| 1305 |
<para> |
| 1306 |
Arguments of the trigger function. Does not exist if <literal>$_TD->{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->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) { |
| 1359 |
return "SKIP"; # skip INSERT/UPDATE command |
| 1360 |
} elsif ($_TD->{new}{v} ne "immortal") { |
| 1361 |
$_TD->{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->{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->{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 — 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> |