codekingpro/portable-devtools
114k
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->{rows}[$i]->{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->{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->{status};47</pre><p>48 To get the number of rows affected, do:49</p><pre class="programlisting">50$nrows = $rv->{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->{status};68 my $nrows = $rv->{processed};69 foreach my $rn (0 .. $nrows - 1) {70 my $row = $rv->{rows}[$rn];71 $row->{i} += 200 if defined($row->{i});72 $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{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, '<', $file # ooh, it's a file access!106 or elog(ERROR, "cannot open $file for reading: $!");107 my @words = <$fh>;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 => $row->{a},117 the_text => 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 > $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 )->{rows}->[0]->{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 << $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 => 2},212 $_[0]213 )->{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>