File: plperl-database.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (160 lines) | stat: -rw-r--r-- 8,108 bytes parent folder | download
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-&gt;{rows}[$i]-&gt;{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-&gt;{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-&gt;{status};</pre>
<p>
       To get the number of rows affected, do:
</p>
<pre class="programlisting">$nrows = $rv-&gt;{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-&gt;{status};
    my $nrows = $rv-&gt;{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv-&gt;{rows}[$rn];
        $row-&gt;{i} += 200 if defined($row-&gt;{i});
        $row-&gt;{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row-&gt;{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, '&lt;', $file # ooh, it's a file access!
        or elog(ERROR, "Can't open $file for reading: $!");
    my @words = &lt;$fh&gt;;
    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 =&gt; $row-&gt;{a},
            the_text =&gt; 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>