PostgreSQL 內(nèi)建函數(shù)

spi_exec_query執(zhí)行一個(gè) SQL 命令并且以哈希引用數(shù)組 的引用的形式返回整個(gè)行集。只有在知道結(jié)果集相對(duì)較小時(shí)才 應(yīng)該使用這個(gè)命令。這里是一個(gè)帶有可選最大行數(shù)的查詢（ SELECT命令）的例子：

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

這會(huì)從表my_table.返回最多 5 行。如果 my_table有一個(gè)列my_column， 可以從結(jié)果的$i行得到值：

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

可以這樣訪問從一個(gè)SELECT查詢返回 的總行數(shù)：

$nrows = $rv->{processed}

這里是使用不同命令類型的一個(gè)例子：

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

你可以這樣訪問命令狀態(tài)（例如SPI_OK_INSERT）：

$res = $rv->{status};

要得到受影響的行數(shù)：

$nrows = $rv->{processed};

這里是一個(gè)完整的例子：

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 結(jié)對(duì)用于可能比較大的行集合，或者用于希望在行到達(dá)時(shí)返回的情況。 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應(yīng)該重復(fù)執(zhí)行直到它返回 undef（表示沒有更多行要讀取）。當(dāng) spi_fetchrow返回undef時(shí)， spi_query返回的游標(biāo)會(huì)自動(dòng)被釋放。如果不 想讀取所有的行，可以調(diào)用 spi_cursor_close來 釋放游標(biāo)。如果沒有這樣做會(huì)導(dǎo)致內(nèi)存泄露。

spi_prepare、spi_query_prepared、 spi_exec_prepared和spi_freeplan 為預(yù)備查詢實(shí)現(xiàn)了相同的功能。spi_prepare接受一個(gè)查詢 字符串，其中包括編好號(hào)的參數(shù)占位符（$1、$2 等）以及參數(shù)類型的字符串 列表：

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

一旦通過調(diào)用spi_prepare準(zhǔn)備好一個(gè)查詢計(jì)劃，就可以在 spi_exec_prepared（返回的結(jié)果和 spi_exec_query相同）或者spi_query_prepared （返回的結(jié)果和spi_query一樣，后面會(huì)被傳給 spi_fetchrow）中用該計(jì)劃來取代字符串查詢。 spi_exec_prepared可選的第二個(gè)參數(shù)是屬性的哈希引用， 當(dāng)前唯一支持的屬性是limit，它限定了一個(gè)查詢返回的最大 行數(shù)。

預(yù)備查詢的有點(diǎn)是可以把一個(gè)準(zhǔn)備好的計(jì)劃用于多次查詢執(zhí)行。不再需要該計(jì)劃后， 可以用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中的參數(shù)下標(biāo)通過 $1、$2、$3 等定義， 這樣避免了用雙引號(hào)來聲明查詢串（容易導(dǎo)致難以捕捉的缺陷）。

另一個(gè)展示spi_exec_prepared中可選參數(shù)用法的例子：

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)

提交或者回滾當(dāng)前事務(wù)。只能在從頂層調(diào)用的過程或者匿名代碼塊（DO命令）中調(diào)用這個(gè)函數(shù)（注意不能通過spi_exec_query或者類似的函數(shù)運(yùn)行SQL命令COMMIT或者ROLLBACK。這樣的工作只能使用這些函數(shù)完成）。在一個(gè)事務(wù)結(jié)束后，一個(gè)新的事務(wù)會(huì)自動(dòng)開始，因此沒有單獨(dú)的函數(shù)來開始新事務(wù)。

這里是一個(gè)例子：

CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
    if ($i % 2 == 0) {
        spi_commit();
    } else {
        spi_rollback();
    }
}
$$;

CALL transaction_test1();

發(fā)出一個(gè)日志或者錯(cuò)誤消息。可用的級(jí)別有 DEBUG、LOG、INFO、 NOTICE、WARNING以及ERROR。 ERROR產(chǎn)生一種錯(cuò)誤情況，如果它沒有被周圍的 Perl 代碼 捕獲，錯(cuò)誤會(huì)傳播到調(diào)用查詢中，導(dǎo)致當(dāng)前事務(wù)或者子事務(wù)被中止。這實(shí)際 上和 Perl 的die 命令相同。其他級(jí)別只產(chǎn)生不同優(yōu)先級(jí)的消息。 特定優(yōu)先級(jí)的消息是被報(bào)告給客戶端、寫到服務(wù)器日志或者兩者都做由 配置變量log_min_messages和 client_min_messages控制。詳見 第 19 章。

返回給定字符串的被適當(dāng)引用后的形式，這種形式能被用作 SQL 語句字符串中的字符串。 嵌入的引號(hào)和反斜線會(huì)被正確地雙寫。注意對(duì) undef 輸入quote_literal 會(huì)返回 undef。如果參數(shù)可能是 undef，quote_nullable通常更合適。

返回給定字符串的被適當(dāng)引用后的形式，這種形式能被用作 SQL 語句字符串中的字符串。 或者在參數(shù)為 undef 時(shí)，返回未引用的串 "NULL"。 嵌入的引號(hào)和反斜線會(huì)被正確地雙寫。

返回給定字符串的被適當(dāng)引用后的形式，這種形式能被用作 SQL 語句字符串 中的標(biāo)識(shí)符。只有在必要時(shí)才增加引號(hào)（即，如果串包含非標(biāo)識(shí)符字符或者是 大小寫折疊的）。嵌入的引號(hào)會(huì)被正確地雙寫。

返回由給定串的內(nèi)容（應(yīng)該用bytea編碼）表示的未轉(zhuǎn)義二進(jìn)制數(shù)據(jù)。

返回給定串的二進(jìn)制數(shù)據(jù)內(nèi)容的bytea編碼形式。

把被引用的數(shù)組的內(nèi)容返回成數(shù)組文字格式（見第 8.15.2 節(jié)） 的一個(gè)串。如果它不是一個(gè)數(shù)組的引用，則不加修改地返回參數(shù)值。如果沒有指定 定界符或者定界符為 undef，則默認(rèn)把", "用作數(shù)組文字的元素 之間的定界符。

把一個(gè) Perl 變量轉(zhuǎn)換為由第二個(gè)參數(shù)傳入的數(shù)據(jù)類型的值，并且返回該值 的字符串表達(dá)。它能正確地處理嵌套數(shù)組和組合類型的值。

把被引用數(shù)組的內(nèi)容返回為數(shù)組構(gòu)造器格式（ 第 4.2.12 節(jié)）的一個(gè)串。其中的個(gè)體值用 quote_nullable引用。如果參數(shù)不是一個(gè)數(shù)組引用，則 返回用quote_nullable引用的該參數(shù)值。

如果給定串的內(nèi)容對(duì)于 Perl 看起來像是數(shù)字則返回真，否則返回假。如果 參數(shù)是 undef 則返回 undef。前導(dǎo)和結(jié)尾的空格會(huì)被忽略。 Inf和Infinity被視作數(shù)字。

如果給定參數(shù)可以被當(dāng)作一個(gè)數(shù)組引用對(duì)待則返回真值，即該參數(shù)的定義為 ARRAY或者PostgreSQL::InServer::ARRAY時(shí)返回 真。否則返回假。