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_query
により返されるものと同様の結果となるspi_exec_prepared
やspi_query
とまったく同じカーソルが返されるspi_query_prepared
(このカーソルは後でspi_fetchrow
に渡すことができます)の中で、その計画を問い合わせ文字列の代わりに使用することができます。
spi_exec_prepared
の省略可能な第二パラメータは属性のハッシュ参照です。
現在サポートされる唯一の属性は、問い合わせで返される最大行数を設定するlimit
です。
プリペアド問い合わせの利点は、1つの準備された計画を複数回使用して問い合わせを実行することができるという点です。
計画が不要になった後、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)
spi_commit()
spi_rollback()
現在のトランザクションをコミットあるいはロールバックします。
これはプロシージャ、あるいはトップレベルから呼ばれた無名コードブロック(DO
コマンド)の中からのみ呼び出すことができます。
(SQLコマンドのCOMMIT
やROLLBACK
をspi_exec_query
などを通して実行することはできない点に注意してください。前述の関数を使って行う必要があります。)
トランザクションが終了した後、新たなトランザクションが自動的に開始されますので、開始するための別途の関数はありません。
以下に例を示します。
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();
elog(level
, msg
)
ログまたはエラーメッセージを発行します。
使用できるレベルは、DEBUG
、LOG
、INFO
、NOTICE
、WARNING
、およびERROR
です。
ERROR
はエラー状態を発生します。
その上位のPerlコードでこのエラーを捕捉しない場合、エラーは問い合わせの呼び出し元まで伝播し、その結果、現在のトランザクションもしくはサブトランザクションはアボートします。
これは実質Perlのdie
コマンドと同じです。
他のレベルは、異なる重要度のメッセージを生成するだけです。
log_min_messagesとclient_min_messages設定パラメータは、特定の重要度のメッセージをクライアントに報告するか、サーバのログに書き出すか、あるいはその両方かを制御します。
詳細は第19章を参照してください。
quote_literal(string
)
与えられた文字列を、SQL文の文字列内で文字列リテラルとして使用するために適切に引用符付けして返します。
埋め込まれた単一引用符およびバックスラッシュは適切に二重化されます。
quote_literal
は入力がundefならばundefを返すことに注意してください。
引数がundefの可能性があるのであれば、quote_nullable
の方が適しています。
quote_nullable(string
)
与えられた文字列を、SQL文の文字列内で文字列リテラルとして使用するために適切に引用符付けして返します。 引数がundefの場合引用符付けされない文字列"NULL"を返します。 埋め込まれた単一引用符およびバックスラッシュは適切に二重化されます。
quote_ident(string
)
与えられた文字列を、SQL文の文字列内で識別子として使用するために適切に引用符付けして返します。 必要な場合(つまり文字列に識別子用ではない文字列が含まれる、または、大文字小文字を保持する場合)のみ引用符が付けられます。 埋め込まれた引用符は適切に二重化されます。
decode_bytea(string
)
与えられた文字列の内容を表す、エスケープのないバイナリデータを返します。
これはbytea
符号化でなければなりません。
encode_bytea(string
)
与えられた文字列の内容をバイナリデータ形式で符号化したbytea
を返します。
encode_array_literal(array
)
encode_array_literal(array
, delimiter
)
参照先の配列の内容を、配列リテラル書式で表した文字列として返します(8.15.2参照)。
配列への参照でない場合は引数の値は変更されません。
配列リテラルの要素間の区切り文字は指定がない、または、undefの場合、デフォルトで",
"です。
encode_typed_literal(value
, typename
)
Perl変数を2番目の引数として渡されたデータ型の値に変換し、その値の文字列表現を返します。 入れ子状の配列や複合型の値を正しく扱います。
encode_array_constructor(array
)
参照先の配列の内容を配列生成書式で表した文字列として返します(4.2.12参照)。
個々の値はquote_nullable
を使用して引用符付けされます。
配列への参照でない場合は、quote_nullable
を使用して引用符付けされた引数の値が返されます。
looks_like_number(string
)
与えられた文字列の内容がPerlの流儀で数値でありそうな場合に真値を、さもなくば偽を返します。
引数がundefならばundefを返します。
先頭の空白、末尾の空白は無視されます。
Inf
およびInfinity
は数値とみなします。
is_array_ref(argument
)
指定された引数が配列参照として扱うことができる場合、つまり、引数のrefがARRAY
またはPostgreSQL::InServer::ARRAY
の場合、真を返します。
さもなくば偽を返します。