Question

从 Perl 函数里访问数据库本身可以通过下面描述的 spi_exec_query 或者一个试验模块 DBD::PgSPI(也可以在 CPAN 镜像站获取)。这个模块让用户可以使用一个 DBI 兼容的叫 $pg_dbh 的数据库句柄，用户可以用它以普通的 DBI 语法执行查询。

PL/Perl 本身目前只提供四个额外的 Perl 命令：

spi_exec_query(query [, max-rows])
spi_query(command)
spi_fetchrow(cursor)
spi_prepare(command, argument types)
spi_exec_prepared(plan)
spi_query_prepared(plan [, attributes], arguments)
spi_cursor_close(cursor)
spi_freeplan(plan)

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, "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);

spi_prepare, spi_query_prepared, spi_exec_prepared, spi_freeplan 为预备查询实现同样的功能。一旦一个查询规划通过调用 spi_prepare 准备好，该规划就可以代替查询字符串，不管是在 spi_exec_prepared 中(与 spi_exec_query 返回的结果相同)还是在 spi_query_prepared 中(与 spi_query 返回的游标相同)，之后可以被传递给 spi_fetchrow 。

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

CREATE OR REPLACE FUNCTION init() RETURNS INTEGER 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 INTEGER 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 ... 表示的，因此避免在双引号中声明查询字符串可能会导致难以发现的臭虫。

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

elog(level, msg)

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