~ubuntu-branches/debian/squeeze/pgadmin3/squeeze

(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>

PL/Perl provides three additional Perl commands:

<dt>

<code xmlns="" class="literal"><code class="function">spi_exec_query</code>(<code>query</code> [, <code>max-rows</code>])</code> <code xmlns="" class="literal"><code class="function">spi_exec_query</code>(<code>command</code>)</code> <code xmlns="" class="literal"><code class="function">spi_query</code>(<code>command</code>)</code> <code class="literal"><code class="function">spi_fetchrow</code>(<code>command</code>)</code>

</dt>

<dd>

<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. You should only use this command when you know

that the result set will be relatively small. Here is an

example of a query (<code class="command">SELECT</code> command) with the

optional maximum number of rows:

<pre class="programlisting">$rv = spi_exec_query('SELECT * FROM my_table', 5);</pre>

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:

<pre class="programlisting">$foo = $rv->{rows}[$i]->{my_column};</pre>

The total number of rows returned from a <code class="command">SELECT</code>

query can be accessed like this:

<pre class="programlisting">$nrows = $rv->{processed}</pre>

Here is an example using a different command type:

<pre class="programlisting">$query = "INSERT INTO my_table VALUES (1, 'test')";

$rv = spi_exec_query($query);</pre>

You can then access the command status (e.g.,

<code class="literal">SPI_OK_INSERT</code>) like this:

<pre class="programlisting">$res = $rv->{status};</pre>

To get the number of rows affected, do:

<pre class="programlisting">$nrows = $rv->{processed};</pre>

Here is a complete example:

<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>

100

101

<code class="literal">spi_query</code> and <code class="literal">spi_fetchrow</code>

102

work together as a pair for row sets which may be large, or for cases

103

where you wish to return rows as they arrive.

104

<code class="literal">spi_fetchrow</code> works only with

105

<code class="literal">spi_query</code>. The following example illustrates how

106

you use them together:

107

108

109

<pre class="programlisting">CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

110

111

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$

112

use Digest::MD5 qw(md5_hex);

113

my $file = '/usr/share/dict/words';

114

my $t = localtime;

115

elog(NOTICE, "opening file $file at $t" );

116

open my $fh, '<', $file # ooh, it's a file access!

117

or elog(ERROR, "Can't open $file for reading: $!");

118

my @words = <$fh>;

119

close $fh;

120

$t = localtime;

121

elog(NOTICE, "closed file $file at $t");

122

chomp(@words);

123

my $row;

124

my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");

125

while (defined ($row = spi_fetchrow($sth))) {

126

return_next({

127

the_num => $row->{a},

128

the_text => md5_hex($words[rand @words])

129

});

130

}

131

return;

132

$$ LANGUAGE plperlu;

133

134

SELECT * from lotsa_md5(500);</pre>

135

136

137

</dd>

138

<dt><code class="literal"><code class="function">elog</code>(<code>level</code>, <code>msg</code>)</code></dt>

139

<dd> Emit a log or error message. Possible levels are

140

<code class="literal">DEBUG</code>, <code class="literal">LOG</code>, <code class="literal">INFO</code>,

141

<code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, and <code class="literal">ERROR</code>.

142

<code class="literal">ERROR</code>

143

raises an error condition; if this is not trapped by the surrounding

144

Perl code, the error propagates out to the calling query, causing

145

the current transaction or subtransaction to be aborted. This

146

is effectively the same as the Perl <code class="literal">die</code> command.

147

The other levels only generate messages of different

148

priority levels.

149

Whether messages of a particular priority are reported to the client,

150

written to the server log, or both is controlled by the

151

<a href="runtime-config-logging.html#guc-log-min-messages">log_min_messages</a> and

152

<a href="runtime-config-logging.html#guc-client-min-messages">client_min_messages</a> configuration

153

variables. See <a href="runtime-config.html" title="Chapter�17.�Server Configuration">Chapter�17, Server Configuration</a> for more

154

information.

155

</dd>

156

</dl></div>

157

158

159

</div></body>

160

</html>

Older »