diff options
Diffstat (limited to 'doc/src/sgml/html/plperl-funcs.html')
| -rw-r--r-- | doc/src/sgml/html/plperl-funcs.html | 308 |
1 files changed, 308 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plperl-funcs.html b/doc/src/sgml/html/plperl-funcs.html new file mode 100644 index 0000000..ce13b69 --- /dev/null +++ b/doc/src/sgml/html/plperl-funcs.html @@ -0,0 +1,308 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>45.1. PL/Perl Functions and Arguments</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plperl.html" title="Chapter 45. PL/Perl — Perl Procedural Language" /><link rel="next" href="plperl-data.html" title="45.2. Data Values in PL/Perl" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">45.1. PL/Perl Functions and Arguments</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plperl.html" title="Chapter 45. PL/Perl — Perl Procedural Language">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plperl.html" title="Chapter 45. PL/Perl — Perl Procedural Language">Up</a></td><th width="60%" align="center">Chapter 45. PL/Perl — Perl Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plperl-data.html" title="45.2. Data Values in PL/Perl">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPERL-FUNCS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">45.1. PL/Perl Functions and Arguments</h2></div></div></div><p> + To create a function in the PL/Perl language, use the standard + <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> + syntax: + +</p><pre class="programlisting"> +CREATE FUNCTION <em class="replaceable"><code>funcname</code></em> (<em class="replaceable"><code>argument-types</code></em>) +RETURNS <em class="replaceable"><code>return-type</code></em> +-- function attributes can go here +AS $$ + # PL/Perl function body goes here +$$ LANGUAGE plperl; +</pre><p> + + 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. + </p><p> + In a PL/Perl procedure, any return value from the Perl code is ignored. + </p><p> + PL/Perl also supports anonymous code blocks called with the + <a class="xref" href="sql-do.html" title="DO"><span class="refentrytitle">DO</span></a> statement: + +</p><pre class="programlisting"> +DO $$ + # PL/Perl code +$$ LANGUAGE plperl; +</pre><p> + + An anonymous code block receives no arguments, and whatever value it + might return is discarded. Otherwise it behaves just like a function. + </p><div class="note"><h3 class="title">Note</h3><p> + 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 place inside + one will be nested. In general, it is far safer to create anonymous + subroutines which you call via a coderef. For more information, see the + entries for <code class="literal">Variable "%s" will not stay shared</code> and + <code class="literal">Variable "%s" is not available</code> in the + <span class="citerefentry"><span class="refentrytitle">perldiag</span></span> man page, or + search the Internet for <span class="quote">“<span class="quote">perl nested named subroutine</span>”</span>. + </p></div><p> + The syntax of the <code class="command">CREATE FUNCTION</code> command requires + the function body to be written as a string constant. It is usually + most convenient to use dollar quoting (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>) for the string constant. + If you choose to use escape string syntax <code class="literal">E''</code>, + you must double any single quote marks (<code class="literal">'</code>) and backslashes + (<code class="literal">\</code>) used in the body of the function + (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a>). + </p><p> + Arguments and results are handled as in any other Perl subroutine: + arguments are passed in <code class="varname">@_</code>, and a result value + is returned with <code class="literal">return</code> or as the last expression + evaluated in the function. + </p><p> + For example, a function returning the greater of two integer values + could be defined as: + +</p><pre class="programlisting"> +CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ + if ($_[0] > $_[1]) { return $_[0]; } + return $_[1]; +$$ LANGUAGE plperl; +</pre><p> + </p><div class="note"><h3 class="title">Note</h3><p> + Arguments will be converted from the database's encoding to UTF-8 + for use inside PL/Perl, and then converted from UTF-8 back to the + database encoding upon return. + </p></div><p> + If an SQL null value<a id="id-1.8.10.9.10.1" class="indexterm"></a> is passed to a function, + the argument value will appear as <span class="quote">“<span class="quote">undefined</span>”</span> in Perl. The + above function definition will not behave very nicely with null + inputs (in fact, it will act as though they are zeroes). We could + add <code class="literal">STRICT</code> to the function definition to make + <span class="productname">PostgreSQL</span> do something more reasonable: + if a null value is passed, the function will not be called at all, + but will just return a null result automatically. Alternatively, + we could check for undefined inputs in the function body. For + example, suppose that we wanted <code class="function">perl_max</code> with + one null and one nonnull argument to return the nonnull argument, + rather than a null value: + +</p><pre class="programlisting"> +CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ + my ($x, $y) = @_; + if (not defined $x) { + return undef if not defined $y; + return $y; + } + return $x if not defined $y; + return $x if $x > $y; + return $y; +$$ LANGUAGE plperl; +</pre><p> + As shown above, to return an SQL null value from a PL/Perl + function, return an undefined value. This can be done whether the + function is strict or not. + </p><p> + Anything in a function argument that is not a reference is + a string, which is in the standard <span class="productname">PostgreSQL</span> + 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 <code class="function">decode_bytea</code> + function can be used to convert an argument of + type <code class="type">bytea</code> into unescaped binary. + </p><p> + Similarly, values passed back to <span class="productname">PostgreSQL</span> + must be in the external text representation format. For example, the + <code class="function">encode_bytea</code> function can be used to + escape binary data for a return value of type <code class="type">bytea</code>. + </p><p> + One case that is particularly important is boolean values. As just + stated, the default behavior for <code class="type">bool</code> values is that they + are passed to Perl as text, thus either <code class="literal">'t'</code> + or <code class="literal">'f'</code>. This is problematic, since Perl will not + treat <code class="literal">'f'</code> as false! It is possible to improve matters + by using a <span class="quote">“<span class="quote">transform</span>”</span> (see + <a class="xref" href="sql-createtransform.html" title="CREATE TRANSFORM"><span class="refentrytitle">CREATE TRANSFORM</span></a>). Suitable transforms are provided + by the <code class="filename">bool_plperl</code> extension. To use it, install + the extension: +</p><pre class="programlisting"> +CREATE EXTENSION bool_plperl; -- or bool_plperlu for PL/PerlU +</pre><p> + Then use the <code class="literal">TRANSFORM</code> function attribute for a + PL/Perl function that takes or returns <code class="type">bool</code>, for example: +</p><pre class="programlisting"> +CREATE FUNCTION perl_and(bool, bool) RETURNS bool +TRANSFORM FOR TYPE bool +AS $$ + my ($a, $b) = @_; + return $a && $b; +$$ LANGUAGE plperl; +</pre><p> + When this transform is applied, <code class="type">bool</code> arguments will be seen + by Perl as being <code class="literal">1</code> or empty, thus properly true or + false. If the function result is type <code class="type">bool</code>, it will be true + or false according to whether Perl would evaluate the returned value as + true. + Similar transformations are also performed for boolean query arguments + and results of SPI queries performed inside the function + (<a class="xref" href="plperl-builtins.html#PLPERL-DATABASE" title="45.3.1. Database Access from PL/Perl">Section 45.3.1</a>). + </p><p> + Perl can return <span class="productname">PostgreSQL</span> arrays as + references to Perl arrays. Here is an example: + +</p><pre class="programlisting"> +CREATE OR REPLACE function returns_array() +RETURNS text[][] AS $$ + return [['a"b','c,d'],['e\\f','g']]; +$$ LANGUAGE plperl; + +select returns_array(); +</pre><p> + </p><p> + Perl passes <span class="productname">PostgreSQL</span> arrays as a blessed + <code class="type">PostgreSQL::InServer::ARRAY</code> object. This object may be treated as an array + reference or a string, allowing for backward compatibility with Perl + code written for <span class="productname">PostgreSQL</span> versions below 9.1 to + run. For example: + +</p><pre class="programlisting"> +CREATE OR REPLACE FUNCTION concat_array_elements(text[]) RETURNS TEXT AS $$ + my $arg = shift; + my $result = ""; + return undef if (!defined $arg); + + # as an array reference + for (@$arg) { + $result .= $_; + } + + # also works as a string + $result .= $arg; + + return $result; +$$ LANGUAGE plperl; + +SELECT concat_array_elements(ARRAY['PL','/','Perl']); +</pre><p> + + </p><div class="note"><h3 class="title">Note</h3><p> + Multidimensional arrays are represented as references to + lower-dimensional arrays of references in a way common to every Perl + programmer. + </p></div><p> + </p><p> + 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: + +</p><pre class="programlisting"> +CREATE TABLE employee ( + name text, + basesalary integer, + bonus integer +); + +CREATE FUNCTION empcomp(employee) RETURNS integer AS $$ + my ($emp) = @_; + return $emp->{basesalary} + $emp->{bonus}; +$$ LANGUAGE plperl; + +SELECT name, empcomp(employee.*) FROM employee; +</pre><p> + </p><p> + 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: + +</p><pre class="programlisting"> +CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text); + +CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$ + return {f2 => 'hello', f1 => 1, f3 => 'world'}; +$$ LANGUAGE plperl; + +SELECT * FROM perl_row(); +</pre><p> + + Any columns in the declared result data type that are not present in the + hash will be returned as null values. + </p><p> + Similarly, output arguments of procedures can be returned as a hash + reference: + +</p><pre class="programlisting"> +CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$ + my ($a, $b) = @_; + return {a => $a * 3, b => $b * 3}; +$$ LANGUAGE plperl; + +CALL perl_triple(5, 10); +</pre><p> + </p><p> + 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 queuing up + the entire result set in memory. You can do this with + <code class="function">return_next</code> as illustrated below. Note that + after the last <code class="function">return_next</code>, you must put + either <code class="literal">return</code> or (better) <code class="literal">return + undef</code>. + +</p><pre class="programlisting"> +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; +</pre><p> + + 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: + +</p><pre class="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 => 1, f2 => 'Hello', f3 => 'World' }, + { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' }, + { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' } + ]; +$$ LANGUAGE plperl; + +SELECT * FROM perl_set(); +</pre><p> + </p><p> + If you wish to use the <code class="literal">strict</code> pragma with your code you + have a few options. For temporary global use you can <code class="command">SET</code> + <code class="literal">plperl.use_strict</code> to true. + This will affect subsequent compilations of <span class="application">PL/Perl</span> + functions, but not functions already compiled in the current session. + For permanent global use you can set <code class="literal">plperl.use_strict</code> + to true in the <code class="filename">postgresql.conf</code> file. + </p><p> + For permanent use in specific functions you can simply put: +</p><pre class="programlisting"> +use strict; +</pre><p> + at the top of the function body. + </p><p> + The <code class="literal">feature</code> pragma is also available to <code class="function">use</code> if your Perl is version 5.10.0 or higher. + </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plperl.html" title="Chapter 45. PL/Perl — Perl Procedural Language">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plperl.html" title="Chapter 45. PL/Perl — Perl Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plperl-data.html" title="45.2. Data Values in PL/Perl">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 45. PL/Perl — Perl Procedural Language </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 45.2. Data Values in PL/Perl</td></tr></table></div></body></html>
\ No newline at end of file |