Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
plperl-builtins.html360 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.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 Vsnapshot" /><link rel="prev" href="plperl-data.html" title="45.2. Data Values in PL/Perl" /><link rel="next" href="plperl-global.html" title="45.4. Global 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.3. Built-in Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plperl-data.html" title="45.2. Data Values in PL/Perl">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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plperl-global.html" title="45.4. Global Values in PL/Perl">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPERL-BUILTINS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">45.3. Built-in Functions <a href="#PLPERL-BUILTINS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plperl-builtins.html#PLPERL-DATABASE">45.3.1. Database Access from PL/Perl</a></span></dt><dt><span class="sect2"><a href="plperl-builtins.html#PLPERL-UTILITY-FUNCTIONS">45.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">45.3.1. Database Access from PL/Perl <a href="#PLPERL-DATABASE" class="id_link">#</a></h3></div></div></div><p>3   Access to the database itself from your Perl function can be done4   via the following functions:5  </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">6      <code class="literal"><code class="function">spi_exec_query</code>(<em class="replaceable"><code>query</code></em> [, <em class="replaceable"><code>limit</code></em>])</code>7      <a id="id-1.8.10.11.2.3.1.1.2" class="indexterm"></a>8     </span></dt><dd><p>9       <code class="function">spi_exec_query</code> executes an SQL command and10returns the entire row set as a reference to an array of hash references.11If <em class="replaceable"><code>limit</code></em> is specified and is greater than zero,12then <code class="function">spi_exec_query</code> retrieves at13most <em class="replaceable"><code>limit</code></em> rows, much as if the query included14a <code class="literal">LIMIT</code> clause.  Omitting <em class="replaceable"><code>limit</code></em>15or specifying it as zero results in no row limit.16      </p><p>17<span class="emphasis"><em>You should only use this command when you know18that the result set will be relatively small.</em></span>  Here is an19example of a query (<code class="command">SELECT</code> command) with the20optional maximum number of rows:21 22</p><pre class="programlisting">23$rv = spi_exec_query('SELECT * FROM my_table', 5);24</pre><p>25        This returns up to 5 rows from the table26        <code class="literal">my_table</code>.  If <code class="literal">my_table</code>27        has a column <code class="literal">my_column</code>, you can get that28        value from row <code class="literal">$i</code> of the result like this:29</p><pre class="programlisting">30$foo = $rv-&gt;{rows}[$i]-&gt;{my_column};31</pre><p>32       The total number of rows returned from a <code class="command">SELECT</code>33       query can be accessed like this:34</p><pre class="programlisting">35$nrows = $rv-&gt;{processed}36</pre><p>37      </p><p>38       Here is an example using a different command type:39</p><pre class="programlisting">40$query = "INSERT INTO my_table VALUES (1, 'test')";41$rv = spi_exec_query($query);42</pre><p>43       You can then access the command status (e.g.,44       <code class="literal">SPI_OK_INSERT</code>) like this:45</p><pre class="programlisting">46$res = $rv-&gt;{status};47</pre><p>48       To get the number of rows affected, do:49</p><pre class="programlisting">50$nrows = $rv-&gt;{processed};51</pre><p>52      </p><p>53       Here is a complete example:54</p><pre class="programlisting">55CREATE TABLE test (56    i int,57    v varchar58);59 60INSERT INTO test (i, v) VALUES (1, 'first line');61INSERT INTO test (i, v) VALUES (2, 'second line');62INSERT INTO test (i, v) VALUES (3, 'third line');63INSERT INTO test (i, v) VALUES (4, 'immortal');64 65CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$66    my $rv = spi_exec_query('select i, v from test;');67    my $status = $rv-&gt;{status};68    my $nrows = $rv-&gt;{processed};69    foreach my $rn (0 .. $nrows - 1) {70        my $row = $rv-&gt;{rows}[$rn];71        $row-&gt;{i} += 200 if defined($row-&gt;{i});72        $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{v}));73        return_next($row);74    }75    return undef;76$$ LANGUAGE plperl;77 78SELECT * FROM test_munge();79</pre><p>80    </p></dd><dt><span class="term">81      <code class="literal"><code class="function">spi_query(<em class="replaceable"><code>command</code></em>)</code></code>82      <a id="id-1.8.10.11.2.3.2.1.2" class="indexterm"></a>83     <br /></span><span class="term">84      <code class="literal"><code class="function">spi_fetchrow(<em class="replaceable"><code>cursor</code></em>)</code></code>85      <a id="id-1.8.10.11.2.3.2.2.2" class="indexterm"></a>86     <br /></span><span class="term">87      <code class="literal"><code class="function">spi_cursor_close(<em class="replaceable"><code>cursor</code></em>)</code></code>88      <a id="id-1.8.10.11.2.3.2.3.2" class="indexterm"></a>89     </span></dt><dd><p>90    <code class="literal">spi_query</code> and <code class="literal">spi_fetchrow</code>91    work together as a pair for row sets which might be large, or for cases92    where you wish to return rows as they arrive.93    <code class="literal">spi_fetchrow</code> works <span class="emphasis"><em>only</em></span> with94    <code class="literal">spi_query</code>. The following example illustrates how95    you use them together:96 97</p><pre class="programlisting">98CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);99 100CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$101    use Digest::MD5 qw(md5_hex);102    my $file = '/usr/share/dict/words';103    my $t = localtime;104    elog(NOTICE, "opening file $file at $t" );105    open my $fh, '&lt;', $file # ooh, it's a file access!106        or elog(ERROR, "cannot open $file for reading: $!");107    my @words = &lt;$fh&gt;;108    close $fh;109    $t = localtime;110    elog(NOTICE, "closed file $file at $t");111    chomp(@words);112    my $row;113    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");114    while (defined ($row = spi_fetchrow($sth))) {115        return_next({116            the_num =&gt; $row-&gt;{a},117            the_text =&gt; md5_hex($words[rand @words])118        });119    }120    return;121$$ LANGUAGE plperlu;122 123SELECT * from lotsa_md5(500);124</pre><p>125    </p><p>126     Normally, <code class="function">spi_fetchrow</code> should be repeated until it127     returns <code class="literal">undef</code>, indicating that there are no more128     rows to read.  The cursor returned by <code class="literal">spi_query</code>129     is automatically freed when130     <code class="function">spi_fetchrow</code> returns <code class="literal">undef</code>.131     If you do not wish to read all the rows, instead call132     <code class="function">spi_cursor_close</code> to free the cursor.133     Failure to do so will result in memory leaks.134    </p></dd><dt><span class="term">135      <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>136      <a id="id-1.8.10.11.2.3.3.1.2" class="indexterm"></a>137     <br /></span><span class="term">138      <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>139      <a id="id-1.8.10.11.2.3.3.2.2" class="indexterm"></a>140     <br /></span><span class="term">141      <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>142      <a id="id-1.8.10.11.2.3.3.3.2" class="indexterm"></a>143     <br /></span><span class="term">144      <code class="literal"><code class="function">spi_freeplan(<em class="replaceable"><code>plan</code></em>)</code></code>145      <a id="id-1.8.10.11.2.3.3.4.2" class="indexterm"></a>146     </span></dt><dd><p>147    <code class="literal">spi_prepare</code>, <code class="literal">spi_query_prepared</code>, <code class="literal">spi_exec_prepared</code>,148    and <code class="literal">spi_freeplan</code> implement the same functionality but for prepared queries.149    <code class="literal">spi_prepare</code> accepts a query string with numbered argument placeholders ($1, $2, etc.)150    and a string list of argument types:151</p><pre class="programlisting">152$plan = spi_prepare('SELECT * FROM test WHERE id &gt; $1 AND name = $2',153                                                     'INTEGER', 'TEXT');154</pre><p>155    Once a query plan is prepared by a call to <code class="literal">spi_prepare</code>, the plan can be used instead156    of the string query, either in <code class="literal">spi_exec_prepared</code>, where the result is the same as returned157    by <code class="literal">spi_exec_query</code>, or in <code class="literal">spi_query_prepared</code> which returns a cursor158    exactly as <code class="literal">spi_query</code> does, which can be later passed to <code class="literal">spi_fetchrow</code>.159    The optional second parameter to <code class="literal">spi_exec_prepared</code> is a hash reference of attributes;160    the only attribute currently supported is <code class="literal">limit</code>, which161    sets the maximum number of rows returned from the query.162    Omitting <code class="literal">limit</code> or specifying it as zero results in no163    row limit.164    </p><p>165    The advantage of prepared queries is that is it possible to use one prepared plan for more166    than one query execution. After the plan is not needed anymore, it can be freed with167    <code class="literal">spi_freeplan</code>:168</p><pre class="programlisting">169CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$170        $_SHARED{my_plan} = spi_prepare('SELECT (now() + $1)::date AS now',171                                        'INTERVAL');172$$ LANGUAGE plperl;173 174CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$175        return spi_exec_prepared(176                $_SHARED{my_plan},177                $_[0]178        )-&gt;{rows}-&gt;[0]-&gt;{now};179$$ LANGUAGE plperl;180 181CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$182        spi_freeplan( $_SHARED{my_plan});183        undef $_SHARED{my_plan};184$$ LANGUAGE plperl;185 186SELECT init();187SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');188SELECT done();189 190  add_time  |  add_time  |  add_time191------------+------------+------------192 2005-12-10 | 2005-12-11 | 2005-12-12193</pre><p>194    Note that the parameter subscript in <code class="literal">spi_prepare</code> is defined via195    $1, $2, $3, etc., so avoid declaring query strings in double quotes that might easily196    lead to hard-to-catch bugs.197    </p><p>198    Another example illustrates usage of an optional parameter in <code class="literal">spi_exec_prepared</code>:199</p><pre class="programlisting">200CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address201                      FROM generate_series(1,3) AS id;202 203CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$204        $_SHARED{plan} = spi_prepare('SELECT * FROM hosts205                                      WHERE address &lt;&lt; $1', 'inet');206$$ LANGUAGE plperl;207 208CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$209        return spi_exec_prepared(210                $_SHARED{plan},211                {limit =&gt; 2},212                $_[0]213        )-&gt;{rows};214$$ LANGUAGE plperl;215 216CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$217        spi_freeplan($_SHARED{plan});218        undef $_SHARED{plan};219$$ LANGUAGE plperl;220 221SELECT init_hosts_query();222SELECT query_hosts('192.168.1.0/30');223SELECT release_hosts_query();224 225    query_hosts226-----------------227 (1,192.168.1.1)228 (2,192.168.1.2)229(2 rows)230</pre><p>231    </p></dd><dt><span class="term">232      <code class="literal"><code class="function">spi_commit()</code></code>233      <a id="id-1.8.10.11.2.3.4.1.2" class="indexterm"></a>234     <br /></span><span class="term">235      <code class="literal"><code class="function">spi_rollback()</code></code>236      <a id="id-1.8.10.11.2.3.4.2.2" class="indexterm"></a>237     </span></dt><dd><p>238       Commit or roll back the current transaction.  This can only be called239       in a procedure or anonymous code block (<code class="command">DO</code> command)240       called from the top level.  (Note that it is not possible to run the241       SQL commands <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code>242       via <code class="function">spi_exec_query</code> or similar.  It has to be done243       using these functions.)  After a transaction is ended, a new244       transaction is automatically started, so there is no separate function245       for that.246      </p><p>247       Here is an example:248</p><pre class="programlisting">249CREATE PROCEDURE transaction_test1()250LANGUAGE plperl251AS $$252foreach my $i (0..9) {253    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");254    if ($i % 2 == 0) {255        spi_commit();256    } else {257        spi_rollback();258    }259}260$$;261 262CALL transaction_test1();263</pre><p>264      </p></dd></dl></div></div><div class="sect2" id="PLPERL-UTILITY-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">45.3.2. Utility Functions in PL/Perl <a href="#PLPERL-UTILITY-FUNCTIONS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt><span class="term">265      <code class="literal"><code class="function">elog(<em class="replaceable"><code>level</code></em>, <em class="replaceable"><code>msg</code></em>)</code></code>266      <a id="id-1.8.10.11.3.2.1.1.2" class="indexterm"></a>267     </span></dt><dd><p>268       Emit a log or error message. Possible levels are269       <code class="literal">DEBUG</code>, <code class="literal">LOG</code>, <code class="literal">INFO</code>,270       <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, and <code class="literal">ERROR</code>.271       <code class="literal">ERROR</code>272        raises an error condition; if this is not trapped by the surrounding273        Perl code, the error propagates out to the calling query, causing274        the current transaction or subtransaction to be aborted.  This275        is effectively the same as the Perl <code class="literal">die</code> command.276        The other levels only generate messages of different277        priority levels.278        Whether messages of a particular priority are reported to the client,279        written to the server log, or both is controlled by the280        <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and281        <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration282        variables. See <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a> for more283        information.284      </p></dd><dt><span class="term">285      <code class="literal"><code class="function">quote_literal(<em class="replaceable"><code>string</code></em>)</code></code>286      <a id="id-1.8.10.11.3.2.2.1.2" class="indexterm"></a>287     </span></dt><dd><p>288        Return the given string suitably quoted to be used as a string literal in an SQL289        statement string. Embedded single-quotes and backslashes are properly doubled.290        Note that <code class="function">quote_literal</code> returns undef on undef input; if the argument291        might be undef, <code class="function">quote_nullable</code> is often more suitable.292      </p></dd><dt><span class="term">293      <code class="literal"><code class="function">quote_nullable(<em class="replaceable"><code>string</code></em>)</code></code>294      <a id="id-1.8.10.11.3.2.3.1.2" class="indexterm"></a>295     </span></dt><dd><p>296        Return the given string suitably quoted to be used as a string literal in an SQL297        statement string; or, if the argument is undef, return the unquoted string "NULL".298        Embedded single-quotes and backslashes are properly doubled.299      </p></dd><dt><span class="term">300      <code class="literal"><code class="function">quote_ident(<em class="replaceable"><code>string</code></em>)</code></code>301      <a id="id-1.8.10.11.3.2.4.1.2" class="indexterm"></a>302     </span></dt><dd><p>303        Return the given string suitably quoted to be used as an identifier in304        an SQL statement string. Quotes are added only if necessary (i.e., if305        the string contains non-identifier characters or would be case-folded).306        Embedded quotes are properly doubled.307      </p></dd><dt><span class="term">308      <code class="literal"><code class="function">decode_bytea(<em class="replaceable"><code>string</code></em>)</code></code>309      <a id="id-1.8.10.11.3.2.5.1.2" class="indexterm"></a>310     </span></dt><dd><p>311        Return the unescaped binary data represented by the contents of the given string,312        which should be <code class="type">bytea</code> encoded.313        </p></dd><dt><span class="term">314      <code class="literal"><code class="function">encode_bytea(<em class="replaceable"><code>string</code></em>)</code></code>315      <a id="id-1.8.10.11.3.2.6.1.2" class="indexterm"></a>316     </span></dt><dd><p>317        Return the <code class="type">bytea</code> encoded form of the binary data contents of the given string.318        </p></dd><dt><span class="term">319      <code class="literal"><code class="function">encode_array_literal(<em class="replaceable"><code>array</code></em>)</code></code>320      <a id="id-1.8.10.11.3.2.7.1.2" class="indexterm"></a>321     <br /></span><span class="term">322      <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>323     </span></dt><dd><p>324        Returns the contents of the referenced array as a string in array literal format325        (see <a class="xref" href="arrays.html#ARRAYS-INPUT" title="8.15.2. Array Value Input">Section 8.15.2</a>).326        Returns the argument value unaltered if it's not a reference to an array.327        The delimiter used between elements of the array literal defaults to "<code class="literal">, </code>"328        if a delimiter is not specified or is undef.329        </p></dd><dt><span class="term">330      <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>331      <a id="id-1.8.10.11.3.2.8.1.2" class="indexterm"></a>332     </span></dt><dd><p>333         Converts a Perl variable to the value of the data type passed as a334         second argument and returns a string representation of this value.335         Correctly handles nested arrays and values of composite types.336       </p></dd><dt><span class="term">337      <code class="literal"><code class="function">encode_array_constructor(<em class="replaceable"><code>array</code></em>)</code></code>338      <a id="id-1.8.10.11.3.2.9.1.2" class="indexterm"></a>339     </span></dt><dd><p>340        Returns the contents of the referenced array as a string in array constructor format341        (see <a class="xref" href="sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS" title="4.2.12. Array Constructors">Section 4.2.12</a>).342        Individual values are quoted using <code class="function">quote_nullable</code>.343        Returns the argument value, quoted using <code class="function">quote_nullable</code>,344        if it's not a reference to an array.345        </p></dd><dt><span class="term">346      <code class="literal"><code class="function">looks_like_number(<em class="replaceable"><code>string</code></em>)</code></code>347      <a id="id-1.8.10.11.3.2.10.1.2" class="indexterm"></a>348     </span></dt><dd><p>349        Returns a true value if the content of the given string looks like a350        number, according to Perl, returns false otherwise.351        Returns undef if the argument is undef.  Leading and trailing space is352        ignored. <code class="literal">Inf</code> and <code class="literal">Infinity</code> are regarded as numbers.353        </p></dd><dt><span class="term">354      <code class="literal"><code class="function">is_array_ref(<em class="replaceable"><code>argument</code></em>)</code></code>355      <a id="id-1.8.10.11.3.2.11.1.2" class="indexterm"></a>356     </span></dt><dd><p>357        Returns a true value if the given argument may be treated as an358        array reference, that is, if ref of the argument is <code class="literal">ARRAY</code> or359        <code class="literal">PostgreSQL::InServer::ARRAY</code>.  Returns false otherwise.360      </p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plperl-data.html" title="45.2. Data Values in PL/Perl">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-global.html" title="45.4. Global Values in PL/Perl">Next</a></td></tr><tr><td width="40%" align="left" valign="top">45.2. Data Values in PL/Perl </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 45.4. Global Values in PL/Perl</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai