diff options
Diffstat (limited to 'doc/src/sgml/html/plperl-builtins.html')
| -rw-r--r-- | doc/src/sgml/html/plperl-builtins.html | 351 |
1 files changed, 351 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plperl-builtins.html b/doc/src/sgml/html/plperl-builtins.html new file mode 100644 index 0000000..8d4bd72 --- /dev/null +++ b/doc/src/sgml/html/plperl-builtins.html @@ -0,0 +1,351 @@ +<?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>44.3. Built-in Functions</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 V1.79.1" /><link rel="prev" href="plperl-data.html" title="44.2. Data Values in PL/Perl" /><link rel="next" href="plperl-global.html" title="44.4. Global Values in PL/Perl" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">44.3. Built-in Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plperl-data.html" title="44.2. Data Values in PL/Perl">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plperl.html" title="Chapter 44. PL/Perl — Perl Procedural Language">Up</a></td><th width="60%" align="center">Chapter 44. PL/Perl — Perl Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plperl-global.html" title="44.4. Global Values in PL/Perl">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="PLPERL-BUILTINS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">44.3. Built-in Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plperl-builtins.html#PLPERL-DATABASE">44.3.1. Database Access from PL/Perl</a></span></dt><dt><span class="sect2"><a href="plperl-builtins.html#PLPERL-UTILITY-FUNCTIONS">44.3.2. Utility Functions in PL/Perl</a></span></dt></dl></div><div class="sect2" id="PLPERL-DATABASE"><div class="titlepage"><div><div><h3 class="title">44.3.1. Database Access from PL/Perl</h3></div></div></div><p> + Access to the database itself from your Perl function can be done + via the following functions: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"> + <code class="literal"><code class="function">spi_exec_query</code>(<em class="replaceable"><code>query</code></em> [, <em class="replaceable"><code>max-rows</code></em>])</code> + <a id="id-1.8.10.11.2.3.1.1.2" class="indexterm"></a> + </span></dt><dd><p> + <code class="literal">spi_exec_query</code> executes an SQL command and +returns the entire row set as a reference to an array of hash +references. <span class="emphasis"><em>You should only use this command when you know +that the result set will be relatively small.</em></span> Here is an +example of a query (<code class="command">SELECT</code> command) with the +optional maximum number of rows: + +</p><pre class="programlisting"> +$rv = spi_exec_query('SELECT * FROM my_table', 5); +</pre><p> + This returns up to 5 rows from the table + <code class="literal">my_table</code>. If <code class="literal">my_table</code> + has a column <code class="literal">my_column</code>, you can get that + value from row <code class="literal">$i</code> of the result like this: +</p><pre class="programlisting"> +$foo = $rv->{rows}[$i]->{my_column}; +</pre><p> + The total number of rows returned from a <code class="command">SELECT</code> + query can be accessed like this: +</p><pre class="programlisting"> +$nrows = $rv->{processed} +</pre><p> + </p><p> + Here is an example using a different command type: +</p><pre class="programlisting"> +$query = "INSERT INTO my_table VALUES (1, 'test')"; +$rv = spi_exec_query($query); +</pre><p> + You can then access the command status (e.g., + <code class="literal">SPI_OK_INSERT</code>) like this: +</p><pre class="programlisting"> +$res = $rv->{status}; +</pre><p> + To get the number of rows affected, do: +</p><pre class="programlisting"> +$nrows = $rv->{processed}; +</pre><p> + </p><p> + Here is a complete example: +</p><pre class="programlisting"> +CREATE TABLE test ( + i int, + v varchar +); + +INSERT INTO test (i, v) VALUES (1, 'first line'); +INSERT INTO test (i, v) VALUES (2, 'second line'); +INSERT INTO test (i, v) VALUES (3, 'third line'); +INSERT INTO test (i, v) VALUES (4, 'immortal'); + +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}; + foreach my $rn (0 .. $nrows - 1) { + 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})); + return_next($row); + } + return undef; +$$ LANGUAGE plperl; + +SELECT * FROM test_munge(); +</pre><p> + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">spi_query(<em class="replaceable"><code>command</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.2.1.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_fetchrow(<em class="replaceable"><code>cursor</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.2.2.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_cursor_close(<em class="replaceable"><code>cursor</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.2.3.2" class="indexterm"></a> + </span></dt><dd><p> + <code class="literal">spi_query</code> and <code class="literal">spi_fetchrow</code> + work together as a pair for row sets which might be large, or for cases + where you wish to return rows as they arrive. + <code class="literal">spi_fetchrow</code> works <span class="emphasis"><em>only</em></span> with + <code class="literal">spi_query</code>. The following example illustrates how + you use them together: + +</p><pre class="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); +</pre><p> + </p><p> + Normally, <code class="function">spi_fetchrow</code> should be repeated until it + returns <code class="literal">undef</code>, indicating that there are no more + rows to read. The cursor returned by <code class="literal">spi_query</code> + is automatically freed when + <code class="function">spi_fetchrow</code> returns <code class="literal">undef</code>. + If you do not wish to read all the rows, instead call + <code class="function">spi_cursor_close</code> to free the cursor. + Failure to do so will result in memory leaks. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">spi_prepare(<em class="replaceable"><code>command</code></em>, <em class="replaceable"><code>argument types</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.3.1.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_query_prepared(<em class="replaceable"><code>plan</code></em>, <em class="replaceable"><code>arguments</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.3.2.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_exec_prepared(<em class="replaceable"><code>plan</code></em> [, <em class="replaceable"><code>attributes</code></em>], <em class="replaceable"><code>arguments</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.3.3.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_freeplan(<em class="replaceable"><code>plan</code></em>)</code></code> + <a id="id-1.8.10.11.2.3.3.4.2" class="indexterm"></a> + </span></dt><dd><p> + <code class="literal">spi_prepare</code>, <code class="literal">spi_query_prepared</code>, <code class="literal">spi_exec_prepared</code>, + and <code class="literal">spi_freeplan</code> implement the same functionality but for prepared queries. + <code class="literal">spi_prepare</code> accepts a query string with numbered argument placeholders ($1, $2, etc) + and a string list of argument types: +</p><pre class="programlisting"> +$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', + 'INTEGER', 'TEXT'); +</pre><p> + Once a query plan is prepared by a call to <code class="literal">spi_prepare</code>, the plan can be used instead + of the string query, either in <code class="literal">spi_exec_prepared</code>, where the result is the same as returned + by <code class="literal">spi_exec_query</code>, or in <code class="literal">spi_query_prepared</code> which returns a cursor + exactly as <code class="literal">spi_query</code> does, which can be later passed to <code class="literal">spi_fetchrow</code>. + The optional second parameter to <code class="literal">spi_exec_prepared</code> is a hash reference of attributes; + the only attribute currently supported is <code class="literal">limit</code>, which sets the maximum number of rows returned by a query. + </p><p> + 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 + <code class="literal">spi_freeplan</code>: +</p><pre class="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 +</pre><p> + Note that the parameter subscript in <code class="literal">spi_prepare</code> is defined via + $1, $2, $3, etc, so avoid declaring query strings in double quotes that might easily + lead to hard-to-catch bugs. + </p><p> + Another example illustrates usage of an optional parameter in <code class="literal">spi_exec_prepared</code>: +</p><pre class="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) +</pre><p> + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">spi_commit()</code></code> + <a id="id-1.8.10.11.2.3.4.1.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">spi_rollback()</code></code> + <a id="id-1.8.10.11.2.3.4.2.2" class="indexterm"></a> + </span></dt><dd><p> + Commit or roll back the current transaction. This can only be called + in a procedure or anonymous code block (<code class="command">DO</code> command) + called from the top level. (Note that it is not possible to run the + SQL commands <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code> + via <code class="function">spi_exec_query</code> or similar. It has to be done + using these functions.) After a transaction is ended, a new + transaction is automatically started, so there is no separate function + for that. + </p><p> + Here is an example: +</p><pre class="programlisting"> +CREATE PROCEDURE transaction_test1() +LANGUAGE plperl +AS $$ +foreach my $i (0..9) { + spi_exec_query("INSERT INTO test1 (a) VALUES ($i)"); + if ($i % 2 == 0) { + spi_commit(); + } else { + spi_rollback(); + } +} +$$; + +CALL transaction_test1(); +</pre><p> + </p></dd></dl></div></div><div class="sect2" id="PLPERL-UTILITY-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">44.3.2. Utility Functions in PL/Perl</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term"> + <code class="literal"><code class="function">elog(<em class="replaceable"><code>level</code></em>, <em class="replaceable"><code>msg</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.1.1.2" class="indexterm"></a> + </span></dt><dd><p> + Emit a log or error message. Possible levels are + <code class="literal">DEBUG</code>, <code class="literal">LOG</code>, <code class="literal">INFO</code>, + <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, and <code class="literal">ERROR</code>. + <code class="literal">ERROR</code> + raises an error condition; if this is not trapped by the surrounding + Perl code, the error propagates out to the calling query, causing + the current transaction or subtransaction to be aborted. This + is effectively the same as the Perl <code class="literal">die</code> command. + The other levels only generate messages of different + priority levels. + Whether messages of a particular priority are reported to the client, + written to the server log, or both is controlled by the + <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and + <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration + variables. See <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a> for more + information. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">quote_literal(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.2.1.2" class="indexterm"></a> + </span></dt><dd><p> + 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 <code class="function">quote_literal</code> returns undef on undef input; if the argument + might be undef, <code class="function">quote_nullable</code> is often more suitable. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">quote_nullable(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.3.1.2" class="indexterm"></a> + </span></dt><dd><p> + 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. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">quote_ident(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.4.1.2" class="indexterm"></a> + </span></dt><dd><p> + 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. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">decode_bytea(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.5.1.2" class="indexterm"></a> + </span></dt><dd><p> + Return the unescaped binary data represented by the contents of the given string, + which should be <code class="type">bytea</code> encoded. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">encode_bytea(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.6.1.2" class="indexterm"></a> + </span></dt><dd><p> + Return the <code class="type">bytea</code> encoded form of the binary data contents of the given string. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">encode_array_literal(<em class="replaceable"><code>array</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.7.1.2" class="indexterm"></a> + <br /></span><span class="term"> + <code class="literal"><code class="function">encode_array_literal(<em class="replaceable"><code>array</code></em>, <em class="replaceable"><code>delimiter</code></em>)</code></code> + </span></dt><dd><p> + Returns the contents of the referenced array as a string in array literal format + (see <a class="xref" href="arrays.html#ARRAYS-INPUT" title="8.15.2. Array Value Input">Section 8.15.2</a>). + 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 "<code class="literal">, </code>" + if a delimiter is not specified or is undef. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">encode_typed_literal(<em class="replaceable"><code>value</code></em>, <em class="replaceable"><code>typename</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.8.1.2" class="indexterm"></a> + </span></dt><dd><p> + Converts a Perl variable to the value of the data type passed as a + second argument and returns a string representation of this value. + Correctly handles nested arrays and values of composite types. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">encode_array_constructor(<em class="replaceable"><code>array</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.9.1.2" class="indexterm"></a> + </span></dt><dd><p> + Returns the contents of the referenced array as a string in array constructor format + (see <a class="xref" href="sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS" title="4.2.12. Array Constructors">Section 4.2.12</a>). + Individual values are quoted using <code class="function">quote_nullable</code>. + Returns the argument value, quoted using <code class="function">quote_nullable</code>, + if it's not a reference to an array. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">looks_like_number(<em class="replaceable"><code>string</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.10.1.2" class="indexterm"></a> + </span></dt><dd><p> + 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. <code class="literal">Inf</code> and <code class="literal">Infinity</code> are regarded as numbers. + </p></dd><dt><span class="term"> + <code class="literal"><code class="function">is_array_ref(<em class="replaceable"><code>argument</code></em>)</code></code> + <a id="id-1.8.10.11.3.2.11.1.2" class="indexterm"></a> + </span></dt><dd><p> + Returns a true value if the given argument may be treated as an + array reference, that is, if ref of the argument is <code class="literal">ARRAY</code> or + <code class="literal">PostgreSQL::InServer::ARRAY</code>. Returns false otherwise. + </p></dd></dl></div></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plperl-data.html" title="44.2. Data Values in PL/Perl">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plperl.html" title="Chapter 44. PL/Perl — Perl Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plperl-global.html" title="44.4. Global Values in PL/Perl">Next</a></td></tr><tr><td width="40%" align="left" valign="top">44.2. Data Values in PL/Perl </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 44.4. Global Values in PL/Perl</td></tr></table></div></body></html>
\ No newline at end of file |