42.3. 內置函數 · PostgreSQL 中文文檔 9.3

# 42.3\. 內置函數 ## 42.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 # 這是訪問文件！ 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(```_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` 接受一個帶有編號的參數占位符的字符串和一個參數類型的字符串列表： ``` $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_query`。`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) ``` ## 42.3.2\. PL/Perl里的效用函數 ``elog(```_level_`, `_msg_`) 發出一條日志或者錯誤信息。可能的級別是`DEBUG`, `LOG`, `INFO`, `NOTICE`, `WARNING`, `ERROR`。`ERROR`拋出一個錯誤條件。如果這個錯誤沒有被周圍的 Perl 代碼捕獲，那么錯誤將傳播到調用的查詢里，導致當前事務或者子事務退出。這實際上相當于 Perl 的`die`命令。其它級別只是生成不同優先級的消息。特定優先級的消息是否報告給客戶端、寫到服務器日志、或者兩個都做，是由配置參數[log_min_messages](#calibre_link-1449)和[client_min_messages](#calibre_link-1448) 控制的。參閱[Chapter 18](#calibre_link-500)獲取更多信息。 ``quote_literal(```_string_`) 適當的返回在一個SQL語句字符串中作為字符串文本引用的給定字符串。嵌入的單引號和反斜杠要加一倍。請注意，`quote_literal`在未定義的輸入上返回未定義；如果參數是未定義的， `quote_nullable`往往是更合適的。 ``quote_nullable(```_string_`) 適當的返回在一個SQL語句字符串中作為字符串文本引用的給定字符串。或者，如果參數是未定義的，返回不加引號的字符串"NULL"。嵌入的單引號和反斜杠要加一倍。 ``quote_ident(```_string_`) 適當的返回在一個SQL語句字符串中作為一個標識符引用的給定字符串。只有在必要時添加引號（也就是，如果字符串包含非標識符字符或是case-folded）。嵌入的單引號和反斜杠要加一倍。 ``decode_bytea(```_string_`) 返回通過給定字符串內容表示的非逃逸二進制數據，應該是`bytea`編碼。 ``encode_bytea(```_string_`) 返回給定字符串的二進制數據內容的`bytea`編碼格式。 ``encode_array_literal(```_array_`) ``encode_array_literal(```_array_`, `_delimiter_`) 返回引用的數組的內容，以在數組里的字符串文本的格式(參閱[Section 8.15.2](#calibre_link-1639))。如果不是一個數組的引用則返回未改變的參數值。如果分隔符沒用指定或是未定義的，則數組文字元素之間的分隔符缺省是"`,` "。 ``encode_typed_literal(```_value_`, `_typename_`) 轉換一個Perl變量為作為第二個參數傳遞的數據類型的值和返回一個這個值的字符串表示。正確處理嵌套數組和復合類型的值。 ``encode_array_constructor(```_array_`) 返回引用的數組的內容，以在數組構造器里的字符串的格式(參閱[Section 4.2.12](#calibre_link-1640))。個別的值用`quote_nullable`引用。如果不是對數組的引用，那么返回參數值用 `quote_nullable`引用。 ``looks_like_number(```_string_`) 根據Perl，如果給定字符串的內容看起來像一個數字則返回真，否則返回假。如果參數是未定義則返回未定義。忽略前置和后置的空格。`Inf`和`Infinity`被認為是數字。 ``is_array_ref(```_argument_`) 如果給定參數可能被視為一個數組引用則返回真，也就是，如果參數的參考是`ARRAY`或 `PostgreSQL::InServer::ARRAY`。否則返回假。