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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>38.2.Database Access from PL/Perl</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plperl.html" title="Chapter38.PL/Perl - Perl Procedural Language">
<link rel="prev" href="plperl.html" title="Chapter38.PL/Perl - Perl Procedural Language">
<link rel="next" href="plperl-data.html" title="38.3.Data Values in PL/Perl">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plperl-database"></a>38.2.Database Access from PL/Perl</h2></div></div></div>
<p> Access to the database itself from your Perl function can be done
via the function <code class="function">spi_exec_query</code> described
below, or via an experimental module
<a href="http://www.cpan.org/modules/by-module/DBD/APILOS/" target="_top"> <code class="literal">DBD::PgSPI</code></a>
(also available at <a href="http://www.cpan.org/SITES.html" target="_top"> <acronym class="acronym">CPAN mirror sites</acronym></a>). This module makes available a
<acronym class="acronym">DBI</acronym>-compliant database-handle named
<code class="varname">$pg_dbh</code> that can be used to perform queries with
normal <acronym class="acronym">DBI</acronym>
syntax.<a name="id731851"></a>
</p>
<p> PL/Perl provides three additional Perl commands:
</p>
<div class="variablelist"><dl>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" 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></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal"><code class="function">spi_exec_query</code>(<em class="replaceable"><code>command</code></em>)</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal"><code class="function">spi_query</code>(<em class="replaceable"><code>command</code></em>)</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal"><code class="function">spi_fetchrow</code>(<em class="replaceable"><code>command</code></em>)</code></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>
<p> <code class="literal">spi_query</code> and <code class="literal">spi_fetchrow</code>
work together as a pair for row sets which may 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, "Can't 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>
</dd>
<dt><span class="term"><code class="literal"><code class="function">elog</code>(<em class="replaceable"><code>level</code></em>, <em class="replaceable"><code>msg</code></em>)</code></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 href="runtime-config-logging.html#guc-log-min-messages">log_min_messages</a> and
<a href="runtime-config-logging.html#guc-client-min-messages">client_min_messages</a> configuration
variables. See <a href="runtime-config.html" title="Chapter17.Server Configuration">Chapter17, <i>Server Configuration</i></a> for more
information.
</p></dd>
</dl></div>
<p>
</p>
</div></body>
</html>
|