44.3. 内置功能

44 .3.1. 从 PL/Perl 访问数据库

您可以通过以下功能从 Perl 函数访问数据库本身：

spi_exec_query(query [, max-rows])
- 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(command)
spi_fetchrow(cursor)
spi_cursor_close(cursor)
- 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 # ooh, it's a file access!
        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_fetchrow返回undef时，由spi_query返回的游标会自动释放。如果您不希望读取所有行，请调用spi_cursor_close释放光标。否则将导致内存泄漏。

spi_prepare(command, argument types)
spi_query_prepared(plan, arguments)
spi_exec_prepared(plan [, attributes], arguments)
spi_freeplan(plan)
- spi_prepare，spi_query_prepared，spi_exec_prepared和spi_freeplan实现相同的功能，但适用于准备好的查询。 spi_prepare接受带有编号的参数占位符($ 1，$ 2 等)和参数类型的字符串列表的查询字符串：

$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 等定义的，因此，请避免在双引号中声明查询字符串，因为这很容易导致难以捕获的错误。

另一个示例说明了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)

44 .3.2. PL/Perl 中的 Util 功能

elog(level, msg)
- 发出日志或错误消息。可能的级别是DEBUG，LOG，INFO，NOTICE，WARNING和ERROR。 ERROR引发错误情况；如果周围的 Perl 代码未捕获到该错误，则该错误将传播到调用查询，从而导致当前事务或子事务中止。这实际上与 Perl die命令相同。其他级别仅生成不同优先级的消息。由log_min_messages和client_min_messages配置变量控制是否将特定优先级的消息报告给 Client 端，写入服务器日志还是同时将这两者控制。有关更多信息，请参见Chapter 19。
quote_literal(string)
- 返回适当引用的给定字符串，以用作 SQL 语句字符串中的字符串 Literals。嵌入式单引号和反斜杠已正确加倍。请注意，quote_literal在 undefImporting 上返回 undef；如果参数可能是 undef，则quote_nullable通常更合适。
quote_nullable(string)
- 返回给定的字符串，该字符串适当地用引号括起来用作 SQL 语句字符串中的字符串 Literals；或者，如果参数为 undef，则返回未加引号的字符串“ NULL”。嵌入式单引号和反斜杠已正确加倍。
quote_ident(string)
- 返回适当引用的给定字符串，以用作 SQL 语句字符串中的标识符。仅在必要时才添加引号(即，如果字符串包含非标识符字符或将大小写折叠)。嵌入式引号正确加倍。
decode_bytea(string)
- 返回由给定字符串的内容表示的未转义的二进制数据，该字符串应进行bytea编码。
encode_bytea(string)
- 返回给定字符串的二进制数据内容的bytea编码形式。
encode_array_literal(array)
encode_array_literal(array, delimiter)
- 以数组 Literals 格式(请参见Section 8.15.2)作为字符串返回引用数组的内容。如果不是对数组的引用，则返回未更改的参数值。如果未指定分隔符或未定义分隔符，则在数组 Literals 的元素之间使用的分隔符默认为“ ,”。
encode_typed_literal(value, typename)
- 将 Perl 变量转换为作为第二个参数传递的数据类型的值，并返回此值的字符串表示形式。正确处理嵌套数组和复合类型的值。
encode_array_constructor(array)
- 以数组构造函数格式(请参见Section 4.2.12)作为字符串返回引用数组的内容。各个值使用quote_nullable引用。如果不是数组的引用，则返回使用quote_nullable引用的参数值。
looks_like_number(string)
- 根据 Perl，如果给定字符串的内容看起来像一个数字，则返回 true，否则返回 false。如果参数为 undef，则返回 undef。前导和尾随空格将被忽略。 Inf和Infinity被视为数字。
is_array_ref(argument)
- 如果给定参数可被视为数组引用，即参数的 ref 为ARRAY或PostgreSQL::InServer::ARRAY，则返回 true 值。否则返回 false。

Docs

Docs4dev

Title here

44.3. 内置功能

44 .3.1. 从 PL/Perl 访问数据库

44 .3.2. PL/Perl 中的 Util 功能