PL/Tcl 言語で関数を作成するには、以下の標準構文を使用して下さい。
CREATE FUNCTION funcname (argument-types) RETURNS return-type AS '
# PL/Tcl function body
' LANGUAGE 'pltcl';
PL/TclU はその言語として'pltclu' を指定しなければならない点以外は同一です。
関数本体は、単なる小さなTclスクリプトです。関数が呼び出された時、引数の値は Tcl スクリプトに $1 ... $n という変数として渡されます。結果は通常return文を使用してTcl のコードから返されます。例えば、2つの整数の内大きな方を返す関数は以下のように定義できます。
CREATE FUNCTION tcl_max (integer, integer) RETURNS integer AS '
if {$1 > $2} {return $1}
return $2
' LANGUAGE 'pltcl' WITH (isStrict);
WITH (isStrict) 句に注意して下さい。これによりプログラマは、入力にNULL値が与えられた場合を検討する手間を省くことができます。NULL が渡された場合、関数はまったく呼び出されず、単に NULL という結果が自動的に返されます。
厳密(strict)でない関数では、引数の実際の値が NULL である場合、対応する $n 変数は空文字列に設定されます。ある引数が NULL かどうかを検出するためには、argisnull を使用して下さい。例えば、引数の片方が NULL、もう片方が非NULLの場合には、NULLではなく、非NULLの引数の方を返すような tcl_max を考えると、以下のようになります。
CREATE FUNCTION tcl_max (integer, integer) RETURNS integer AS '
if {[argisnull 1]} {
if {[argisnull 2]} { return_null }
return $2
}
if {[argisnull 2]} { return $1 }
if {$1 > $2} {return $1}
return $2
' LANGUAGE 'pltcl';
上で示した通り、NULL 値を PL/Tcl関数から返すためには、 return_null を実行して下さい。これは、関数が厳密かどうかに関係なく、実行することができます。
複合型の引数は、Tcl 配列としてプロシージャに渡されます。配列の要素名は複合型の属性名です。渡された行の属性がNULL値の場合、その属性は配列内には現れません! ここで、PL/Tcl で作成された overpaid_2 function を定義する例を示します(この関数は古い PostgreSQL にも記載されています)。
CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
if {200000.0 < $1(salary)} {
return "t"
}
if {$1(age) < 30 && 100000.0 < $1(salary)} {
return "t"
}
return "f"
' LANGUAGE 'pltcl';
今のところ、複合型の戻り値を返す機能はサポートされていません。
PL/Tcl 関数スクリプトに与えられる引数の値は、単に、テキスト形式(SELECT文によりそれを表示した場合と同じ形式)に変換された入力引数です。逆に、return コマンドは、その関数宣言における戻り値の型の入力形式として受け付けることができる、任意の文字列を受け付けます。ですから、PL/Tcl プログラマはデータの値を単なるテキストであるかのように操作することができます。
あるプロシージャの複数の呼び出し間で保持される、もしくは、異なるプロシージャ間で共有されるような、いくつかのグローバルな状態データを持つことが有意な場合があります。あるバックエンド内で実行される全ての PL/Tcl プロシージャは同一の安全な Tcl インタプリタを共有しますので、これは簡単に実現できます。ですから、全てのグローバルな Tcl 変数は、全てのPL/Tcl プロシージャ呼び出しでアクセスすることができ、そして、その SQL クライアントの接続の間、永続化されます(PL/TclU 関数はグローバルデータを共有しますが、異なるインタプリタ内で実行されますので、PL/Tcl 関数間で通信することができません)。
PL/Tclプロシージャが予期しない作動に巻き込まれないようにするために、 upvarコマンドを使用することによって、各関数でアクセスできるグローバルな配列を作成することができます。この変数のグローバル名はプロシージャの内部名で、ローカル名はGDとなります。プロシージャの固有の状態データではGDを使用することを推奨します。複数のプロシージャで共用させる値の時のみ、通常のTclのグローバル変数を使用して下さい。
上述の spi_execp の例の中に GD の使用例があります。
下記のコマンドは、PL/Tclプロシージャ内からデータベースアクセスを行う時に使用できるコマンドです。
文字列として与えられた SQL 問い合わせを実行します。問い合わせ内のエラーは、エラーの発生となります。さもなければ、このコマンドの戻り値は問い合わせによって処理(選択、挿入、更新、削除)された行数、または、問い合わせがユーティリティ文の場合はゼロとなります。更に、問い合わせが SELECT 文の場合、選択された列の値は以下のように Tcl の変数に格納されます。
-count オプションの値は、spi_exec に対し、その問い合わせで処理する最大行数を指示します。これにより、問い合わせをカーソルとして設定し、FETCH n を実行することと同じことができます。
問い合わせ文が SELECT 文の場合、SELECT の結果得られた列の値は、列と同じ名前の Tcl 変数に格納されます。-array オプションが付与された場合は、列の値は指定した名前の連想配列に格納され、その配列のインデックスとして SELECT された列の名前が使用されます。
問い合わせ文が SELECT 文、かつ、loop-body スクリプトが付与されなかった場合、結果の内最初の行だけがTcl 変数に格納されます。他にも行があったとしても、それらは無視されます。SELECT 文が行を返さなかった場合は、変数への格納は発生しません(spi_exec の戻り値を検査することで、これを検出することができます)。以下に例を示します。
spi_exec "SELECT count(*) AS cnt FROM pg_proc"
この例は、pg_proc システムカタログの行数を Tcl 変数 $cnt に設定します。
loop-body オプション引数が付与された場合、それは、SELECT の結果内の行それぞれに対して一度だけ実行される小さな Tclスクリプトを意味します(注意:loop-body はSELECT 以外の問い合わせで付与された場合は無視されます)。処理中の行のフィールド値は、各繰返しの前にTcl変数に格納されます。以下に例を示します。
spi_exec -array C "SELECT * FROM pg_class" {
elog DEBUG "have table $C(relname)"
}
この例では、pg_class の全ての行に対して、DEBUG ログメッセージを出力します。この機能は他の Tcl の繰返し構造でも同様に動作します。特に loop body 内のcontinue と break は通常通り動作します。
SELECT文の結果、フィールドが NULL であった場合、対象となる変数は代入(set)されずに、"削除(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でもパーサがバックスラッシュを処理するため、関数を作成する時に 2 重に表記する必要があることに注意して下さい。spi_prepare に与える問い合わせ文字列の内側で、$n 印が確実にそのまま spi_prepare に渡され、Tcl 変数の代入による置き換えが起こらないようにバックスラッシュが必要です。
直前のspi_exec または spi_execpによる問い合わせが単一行の INSERT 文の場合、その問い合わせによって挿入された行の OID を返します(さもなければ、ゼロを返します)。
指定された文字列内の全ての単一引用符とバックスラッシュ文字を二重化します。spi_exec や spi_prepareで与えられたSQL問い合わせに挿入される予定の文字列を安全に引用するためにこれを使用することができます。例えば、以下のような問い合わせ文字列を考えます。
"SELECT '$val' AS ret"
ここで、Tcl 変数 valに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、NOTICE、ERROR、 FATAL です。DEBUG と NOTICE は単に与えられたメッセージをpostmaster のログに出力(、そして NOTICEの場合はクライアントへも出力)します。ERROR はエラー状態を発生し、その後の関数の実行部分は処理されません。また、現在のトランザクションは中断されます。FATAL はトランザクションを中断し、現在のバックエンドをシャットダウンさせます(PL/Tcl においてこのエラーレベルを使用すべき理由はおそらく存在しませんが、完全性のために用意されています)。
トリガプロシージャはPL/Tclで作成することができます。 PostgreSQL の慣習通り、トリガとして呼び出されるプロシージャは、引数をとらない、opaque 型を返す関数として宣言しなければなりません。
トリガマネージャからの情報は、以下の変数内に格納されてプロシージャ本体に渡されます。
CREATE TRIGGER 文によるトリガ名。
そのトリガプロシージャ呼び出しが発生したテーブルのオブジェクトID。
先頭に空のリスト要素を持つ、テーブルのフィールド名の Tcl リスト。Tcl のlsearch コマンドを使用してそのリストから要素名を検索することで、最初の列を1とした要素番号が返ります。これは、 PostgreSQL による通常のフィールドの番号づけと同じです。
トリガ呼び出しの種類に応じた、BEFORE または AFTER という文字列。
トリガ呼び出しの種類に応じた、ROW または STATEMENT という文字列。
トリガ呼び出しの種類に応じた、INSERT、 UPDATE または DELETE という文字列。
INSERT/UPDATE 動作の場合は新しいテーブル行の値を、DELETE 動作の場合は空を持つ連想配列。配列のインデックスはフィールド名です。NULL のフィールドはこの配列内には現れません!
UPDATE/DELETE 動作の場合は古いテーブル行の値を、INSERT 動作の場合は空を持つ連想配列。配列のインデックスはフィールド名です。NULL のフィールドはこの配列内には現れません!
CREATE TRIGGER 文で指定された、プロシージャへの引数のTclリスト。この引数は、プロシージャ本体から $1 ... $n としてもアクセスすることができます。
トリガプロシージャからの戻り値は、OKという文字列、 SKIPという文字列、array get Tcl コマンドによって返されるリストの内の1つをとることができます。戻り値が OK の場合、トリガを発行した操作(INSERT/UPDATE/DELETE) は正常に処理されます。 SKIP はトリガマネージャにこの行に対する操作を何も出力せずに中止するように通知します。リストが返された場合は、PL/Tclに対し、トリガマネージャによって与えられた $NEW ではなく変更した行をトリガマネージャに返すように通知します(これは INSERT/UPDATEの場合のみ動作します)。いうまでもありませんが、これらはすべて、トリガが BEFORE、かつ、 FOR EACH ROW の時にのみ意味があります。さもなければ、戻り値は無視されます。
ここで、テーブル内の整数値としてその行に対する更新数を記録させる、小さめのトリガプロシージャの例を示します。新規の行が挿入された場合は、その値は 0 に初期化され、その後の各更新操作時に1が加算されます。
CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS '
switch $TG_op {
INSERT {
set NEW($1) 0
}
UPDATE {
set NEW($1) $OLD($1)
incr NEW($1)
}
default {
return OK
}
}
return [array get NEW]
' LANGUAGE 'pltcl';
CREATE TABLE mytab (num integer, description text, modcnt integer);
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
トリガプロシージャ自身は列名を認識していない点に注目して下さい。これは、トリガの引数として与えられます。これにより、このトリガプロシージャを別のテーブルで再利用することができます。
PL/Tcl は、使用時に自動的にTclのコードを読み込む機能があります。これは、pltcl_modules という特殊なテーブルを認識します。このテーブルには、Tcl コードのモジュールが含まれているとみなします。このテーブルが存在する場合、そのテーブルから unknown モジュールが取り出され、Tcl インタプリタの生成後即座に、そのインタプリタに読み込まれます。
実際、unknown モジュールには必要な任意の初期化コードを含めることができますが、通常は、そこに Tcl "unknown" プロシージャを定義します。このプロシージャは Tcl が呼び出されたプロシージャ名を認識できなかった場合に常に呼び出されます。PL/Tcl 標準のこのプロシージャは、必要なプロシージャを定義しているはずの pltcl_modules からのモジュール検索を試みます。プロシージャが検出された場合、インタプリタに読み込まれ、その後、元々試みられたプロシージャ呼び出しを実行することが許されます。二次的な pltcl_modfuncs テーブルは、どの関数がどのモジュールで定義されているかに関するインデックスを提供します。これにより検索がかなり高速になります。
PostgreSQL の配布には、これらのテーブル管理用のサポートスクリプト、pltcl_loadmod、 pltcl_listmodおよびpltcl_delmod が含まれています。同様に標準の unknown モジュールのソースshare/unknown.pltclも含まれています。自動読み込み機構をサポートさせるためには、あらかじめ各データベースにこのモジュールを読み込ませる必要があります。
pltcl_modules および pltcl_modfuncs テーブルは全ユーザから読みとり可能でなければなりません。しかし、その所有者をデータベース管理者とし、データベース管理者のみが書き込み可能とする方が良いでしょう。
PostgreSQLでは、その関数の引数の数または引数の型が異っていれば、同じ関数名を使用することが可能です。しかし、Tclではプロシージャ名の重複は許されません。PL/Tclでは、プロシージャ名の一部に、そのプロシージャの pg_proc の行のIDを持たせた内部的な Tcl プロシージャ名を作成することで対応しています。従って、こういった異なる引数の型を持つ同じ名前の PostgreSQL 関数は、Tcl のプロシージャとは異なります。PL/Tcl プログラマから見ますと、通常は問題にはありませんが、デバッグの際に表面に現れます。