38.5. PL/Tclからのデータベースアクセス

文字列として与えられたSQL問い合わせを実行します。 コマンド内のエラーは、エラーの発生となります。 さもなければ、このspi_execの戻り値はコマンドによって処理(選択、挿入、更新、削除)された行数、または、コマンドがユーティリティ文の場合はゼロとなります。 更に、コマンドがSELECT文の場合、選択された列の値は以下のようにTclの変数に格納されます。

-countオプションの値は、spi_execに対し、そのコマンドで処理する最大行数を指示します。 これにより、問い合わせをカーソルとして設定し、FETCH nを実行することと同じことができます。

コマンドがSELECT文の場合、その結果得られた列の値は、列名にちなんだ名前のTcl変数に格納されます。 -arrayオプションが付与された場合は、列の値は指定した名前の連想配列に格納され、その配列のインデックスとして列名が使用されます。

問い合わせ文がSELECT文、かつ、loop-bodyスクリプトが付与されなかった場合、結果の内最初の行だけがTcl変数に格納されます。 他にも行があったとしても、それらは無視されます。 問い合わせが行を返さなかった場合は、変数への格納は発生しません。 (spi_execの戻り値を検査することで、これを検出することができます。) 以下に例を示します。

spi_exec "SELECT count(*) AS cnt FROM pg_proc"

これは、$cnt Tcl変数を、pg_procシステムカタログの行数に設定します。

loop-bodyオプション引数が付与された場合、それは、問い合わせの結果内の行それぞれに対して一度だけ実行される小さなTclスクリプトです。 (loop-bodyはSELECT以外の問い合わせで付与された場合は無視されます。) 処理中の行の列値は、各繰返しの前にTcl変数に格納されます。 以下に例を示します。

spi_exec -array C "SELECT * FROM pg_class" {
    elog DEBUG "have table $C(relname)"
}

これは、pg_classの各行に対してログメッセージを出力します。 この機能は他のTclの繰返し構文でも同様に動作します。 特にループ本体内のcontinueとbreakは通常通り動作します。

問い合わせの結果、列がNULLであった場合、対象となる変数は代入されずに、"削除(unset)"されます。

後の実行のために問い合わせ計画の準備、保存を行います。 保存された計画は現在のセッションが終了するまで保持されます。

問い合わせはパラメータ、つまり、計画が実際に実行される時に常に与えられる値用のプレースホルダを持つことができます。 問い合わせ文字列の中では、$1 ... $nというシンボルを使用して引数を参照して下さい。 問い合わせがパラメータを使用する場合、Tclのリストとしてパラメータの型名を指定する必要があります。 (パラメータを使用しない場合はtypelistには空のリストを指定して下さい。) 今のところ、このパラメータの型はpg_typeシステムテーブル内に存在する内部型名、例えば、integerではなく、int4で識別しなければなりません。

spi_prepareの戻り値は問い合わせIDです。 このIDは後のspi_execpで使用されます。 使用例についてはspi_execpを参照して下さい。

spi_prepareにより事前に準備された問い合わせを実行します。 queryidはspi_prepareにより返されたIDです。 その問い合わせがパラメータを参照する場合、value-listを与える必要があります。 これは、そのパラメータの実際の値を持つTclのリストです。 このリストの長さは、事前にspi_prepareで指定した引数型のリストの長さと同じでなければなりません。 問い合わせにパラメータがない場合は、value-listを省略して下さい。

-nullsオプションの値は、空白文字と'n'という文字からなる文字列で、spi_execpに対し、どの引数がNULL値かを示します。 指定された場合、その文字列の長さはvalue-listの長さと正確に一致していなければなりません。 指定されない場合は、全てのパラメータの値は非NULLです。

問い合わせとそのパラメータをどこで指定するのかという点を除き、spi_execpはspi_execと同様に動作します。 -count、-array、loop-bodyオプションも、そして、結果の値も同じです。

ここで、準備済みの計画を使用した、PL/Tcl関数の例を示します。

CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS '
    if {![ info exists GD(plan) ]} {
        # prepare the saved plan on the first call
        set GD(plan) [ spi_prepare \\
                "SELECT count(*) AS cnt FROM t1 WHERE num >= \\$1 AND num <= \\$2" \\
                [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $cnt
' LANGUAGE pltcl;

Tclが認識すべき各バックスラッシュは、CREATE FUNCTIONでもメインパーサがバックスラッシュを処理するため、関数を作成する時に二重に表記する必要があることに注意して下さい。 spi_prepareに与える問い合わせ文字列の内側では、$n印が確実にそのままspi_prepareに渡され、Tcl変数の代入による置き換えが起こらないようにバックスラッシュが必要です。

直前のspi_execまたはspi_execpによる問い合わせが単一行のINSERT文の場合、その問い合わせによって挿入された行のOIDを返します。 (さもなければ、ゼロを返します。)

指定された文字列内の全ての単一引用符とバックスラッシュ文字を二重化します。 spi_execやspi_prepareで与えられたSQL問い合わせに挿入される予定の文字列を安全に引用するためにこれを使用することができます。 例えば、以下のような問い合わせ文字列を考えます。

"SELECT '$val' AS ret"

ここで、val Tcl変数にdoesn'tが実際に含まれているものとします。 これは最終的に以下の問い合わせ文字列になってしまいます。

SELECT 'doesn't' AS ret

これでは、spi_execまたはspi_prepareの実行中に解析エラーが発生してしまいます。 実行したい問い合わせは以下のようなものです。

SELECT 'doesn''t' AS ret

これは、PL/Tclでは以下により形成することができます。

"SELECT '[ quote $val ]' AS ret"

spi_execpの持つ1つの利点は、パラメータはSQL問い合わせ文字列の一部として解析されることがありませんので、このようにパラメータの値を引用する必要がないことです。

ログまたはエラーメッセージを発行します。 使用できるレベルは、DEBUG、LOG、INFO、NOTICE、WARNING、ERROR、およびFATALです。 これらのほとんどは、C関数のelogと同じように、単に与えられたメッセージを出力するだけです。 ERRORはエラー状態を発生し、その後の関数の実行部分は処理されません。 また、現在のトランザクションは中断されます。 FATALはトランザクションを中断し、現在のセッションをシャットダウンさせます。 (PL/Tcl関数においてこのエラーレベルを使用すべき理由はおそらく存在しませんが、完全性のために用意されています。)