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`。否则返回假。