40.2. PL/Perlからのデータベースアクセス

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および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_prepare、spi_query_prepared、spi_exec_prepared、spi_freeplanは、準備済み問い合わせ用に同様の機能を実装します。 spi_prepareを呼び出して問い合わせ計画の準備が終わると、 spi_exec_queryにより返されるものと同様の結果となるspi_exec_preparedやspi_queryとまったく同じカーソルが返されるspi_query_preparedを使用して、その計画を文字列問い合わせの代わりに使用することができます。

準備済み問い合わせの利点は、1つの準備された計画を複数回使用して問い合わせを実行することができるという点です。 計画が不要になった後、spi_freeplanを使用して、計画を開放することができます。

CREATE OR REPLACE FUNCTION init() RETURNS INTEGER 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 INTEGER 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_fetchrowは、読み取る行がもう存在しないことを意味するundefが返されるまで、繰り返されるはずです。 spi_fetchrowがundefを返す時、カーソルは自動的に開放されます。 返されるすべての行を読み取る必要がなければ、spi_cursor_closeを呼び出してカーソルを開放してください。 これに失敗するとメモリリークが発生します。

ログまたはエラーメッセージを発行します。 使用できるレベルは、DEBUG、LOG、INFO、NOTICE、WARNING、およびERRORです。 ERRORはエラー状態を発生します。 その上位のPerlコードでこのエラーを捕捉しない場合、エラーは問い合わせの呼び出し元まで伝播し、その結果、現在のトランザクションもしくは副トランザクションはアボートします。 これは実質Perlのdieコマンドと同じです。 他のレベルは、異なる重要度のメッセージを生成するだけです。 log_min_messagesとclient_min_messages設定パラメータは、特定の重要度のメッセージをクライアントに報告するか、サーバのログに書き出すか、あるいはその両方かを制御します。 詳細は第18章を参照してください。