1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
|
<?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.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 15.6 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</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</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>limit</code></em>])</code>
<a id="id-1.8.10.11.2.3.1.1.2" class="indexterm"></a>
</span></dt><dd><p>
<code class="function">spi_exec_query</code> executes an SQL command and
returns the entire row set as a reference to an array of hash references.
If <em class="replaceable"><code>limit</code></em> is specified and is greater than zero,
then <code class="function">spi_exec_query</code> retrieves at
most <em class="replaceable"><code>limit</code></em> rows, much as if the query included
a <code class="literal">LIMIT</code> clause. Omitting <em class="replaceable"><code>limit</code></em>
or specifying it as zero results in no row limit.
</p><p>
<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 from the query.
Omitting <code class="literal">limit</code> or specifying it as zero results in no
row limit.
</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">45.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 20. Server Configuration">Chapter 20</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 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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 45.4. Global Values in PL/Perl</td></tr></table></div></body></html>
|
