42.3. 内置函数

spi_exec_query执行一个 SQL 命令然后把整个结果集当作一个指向散列引用的引用返回。 只有在你知道结果集相对比较小的时候才能用这个命令。 下面是一个带有额外的最大行数的查询(SELECT命令)的例子。

$rv = spi_exec_query('SELECT * FROM my_table', 5);

它从my_table里返回最多 5 行。如果my_table 有一个字段是my_column,那么可以用下面的方法从结果的第$i行获取其值:

$foo = $rv->{rows}[$i]->{my_column};

从一个SELECT查询返回的总行数可以这样访问:

$nrows = $rv->{processed}

这里是一个使用其它命令的例子:

$query = "INSERT INTO my_table VALUES (1, 'test')";
$rv = spi_exec_query($query);

你可以用下面方法访问状态(如SPI_OK_INSERT):

$res = $rv->{status};

这样获取影响的行数:

$nrows = $rv->{processed};

下面是一个完整的例子:

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

spi_query和spi_fetchrow一起用于处理那些行集可能比较大, 或者是在你收到行的时候就返回的场合。spi_fetchrow只能 和spi_query一起使用。下面的例子演示了如何使用:

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 # 这是访问文件!
 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);

通常,应当重复spi_fetchrow直到其返回undef 以表示没有行可以读取了,此时由spi_query返回的游标将被自动释放。 如果你确实不想读取所有行,可以明确调用spi_cursor_close来释放游标, 否则将会导致内存泄漏。

spi_prepare, spi_query_prepared, spi_exec_prepared, spi_freeplan为预备查询实现同样的功能。spi_prepare 接受一个带有编号的参数占位符的字符串和一个参数类型的字符串列表:

$plan = spi_prepare('SELECT * FROM test WHERE id > 1ドル AND name = 2ドル',
 'INTEGER', 'TEXT');

一旦一个查询规划通过调用spi_prepare准备好,该规划就可以代替查询字符串, 不管是在spi_exec_prepared中(与spi_exec_query返回的结果相同) 还是在spi_query_prepared中(与spi_query返回的游标相同), 之后可以被传递给spi_fetchrow。spi_exec_prepared 可选的第二个参数是一个属性的哈希引用;当前唯一支持的属性是limit, 设置查询返回行的最小数量。

预备查询的好处是可以为多个查询的执行使用一个预备规划。在规划不再被需要之后, 可以通过spi_freeplan释放:

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日

注意,spi_prepare中的参数是通过 1,ドル 2,ドル 3ドル ... 表示的, 因此避免在双引号中声明查询字符串,那样可能会导致难以发现的Bug。

另外一个说明spi_exec_prepared里的可选参数的使用的例子:

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)

发出一条日志或者错误消息。可能的级别是DEBUG, LOG, INFO, NOTICE, WARNING, ERROR。ERROR抛出一个错误条件。 如果这个错误没有被周围的 Perl 代码捕获,那么错误将传播到调用的查询里, 导致当前事务或者子事务退出。这实际上相当于 Perl 的die命令。 其它级别只是生成不同优先级的消息。特定优先级的消息是否报告给客户端、写到服务器日志、 或者两个都做,是由配置参数log_min_messages和client_min_messages 控制的。参阅第 18 章获取更多信息。

适当的返回在一个SQL语句字符串中作为字符串文本引用的给定字符串。嵌入的单引号和反斜杠要加一倍。 请注意,quote_literal在未定义的输入上返回未定义;如果参数是未定义的, quote_nullable往往是更合适的。

适当的返回在一个SQL语句字符串中作为字符串文本引用的给定字符串。或者,如果参数是未定义的, 返回不加引号的字符串"NULL"。嵌入的单引号和反斜杠要加一倍。

适当的返回在一个SQL语句字符串中作为一个标识符引用的给定字符串。只有在必要时添加引号 (也就是,如果字符串包含非标识符字符或是case-folded)。嵌入的单引号和反斜杠要加一倍。

返回通过给定字符串内容表示的非转义二进制数据,应该是bytea编码。

返回给定字符串的二进制数据内容的bytea编码格式。

返回引用的数组的内容,以在数组里的字符串文本的格式(参阅第 8.15.2 节)。 如果不是一个数组的引用则返回未改变的参数值。如果分隔符没用指定或是未定义的, 则数组文字元素之间的分隔符缺省是", "。

转换一个Perl变量为作为第二个参数传递的数据类型的值和返回一个这个值的字符串表示。 正确处理嵌套数组和复合类型的值。

返回引用的数组的内容,以在数组构造器里的字符串的格式(参阅第 4.2.12 节)。 个别的值用quote_nullable引用。如果不是对数组的引用,那么返回参数值用 quote_nullable引用。

根据Perl,如果给定字符串的内容看起来像一个数字则返回真,否则返回假。如果参数是未定义则返回未定义。 忽略前置和后置的空格。Inf和Infinity被认为是数字。

如果给定参数可能被视为一个数组引用则返回真,也就是,如果参数的参考是ARRAY或 PostgreSQL::InServer::ARRAY。否则返回假。