PL/pgSQLで開発する1つの良い方法は、関数を作成するのに自分の好きなテキストエディタを使い、もう1つのウィンドウでpsqlを使用して関数を読み込ませて試験を行うことです。
この方法で行う場合にはCREATE OR REPLACE FUNCTION
を使用して関数を作成する方が良いでしょう。
この方法でファイルを再読み込みすると、関数定義を更新することができます。
例えば以下のようにします。
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ .... $$ LANGUAGE plpgsql;
psqlを実行し、以下のように関数定義ファイルを読み込み、または再読み込みすることができます。
\i filename.sql
その後すぐに、関数を試験するためにSQLコマンドを発行することができます。
PL/pgSQLにおける開発のもう1つの良い方法は、手続き言語の開発機能を持つGUIデータベースアクセスツールを使用することです。 他にもありますが、pgAdminがこうしたツールの一例です。 こうしたツールは、単一引用符をエスケープさせたり、関数の作り直しやデバッグが容易に行えたりする便利な機能をよく持っています。
PL/pgSQL関数のコードはCREATE FUNCTION
内で文字列リテラルとして指定されます。
単一引用符で囲む通常のやり方で文字列リテラルを記述する時、関数本体内部の全ての単一引用符を二重化しなければなりません。
同様に、全てのバックスラッシュを二重化しなければなりません。
なお、文字列としてエスケープする構文が使用されると仮定します。
引用符を単に重ねるやり方は最も冗長であり、簡単に想像できると思いますが、複雑な状態では数個以上の隣接した引用符が必要となるため、コードを率直には理解しにくくなります。
それに代わって推奨されるのは、関数本体を「ドル引用符」の文字列リテラルとして記述することです(4.1.2.4を見てください)。
ドル引用符を用いるやり方では他の引用符を二重化する必要はありませんが、それぞれの入れ子になった階層ごとに異なったドル引用符による区切り符号を用いなければなりません。
例えば、CREATE FUNCTION
コマンドを以下のように記述しても構いません。
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ .... $PROC$ LANGUAGE plpgsql;
このやり方では、SQLコマンドの中で単純なリテラル文字列に対して引用符を使用でき、文字列として集積したSQLコマンドの断片を区切るために$$
を使用できます。
もし$$
を含んだテキストを引用符で囲む時は、$Q$
のような記述を使用できます。
以下の表はドル引用符を用いない時の引用符の記述法を示したものです。 ドル引用符を用いる以前における引用符の記述を理解するのに、この表は役立つと思われます。
関数本体の先頭と末尾。 以下に例を示します。
CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE plpgsql;
関数本体内部では引用符は必ずペアで現れます。
関数本体内部の文字列リテラル用。 以下に例を示します。
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
ドル引用符を用いる時は、次のように記述します。
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
どちらもPL/pgSQLパーサから見ると同一となります。
関数本体内部の文字列リテラル内の単一引用符がある場合。 以下に例を示します。
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
実際にa_output
に追加される値は、 AND name LIKE 'foobar' AND xyz
です。
ドル引用符を用いる時は、次のように記述します。
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
なお、ドル引用符の区切り文字は$$
だけとは限らないことに注意してください。
関数本体内部の文字列内の単一引用符が、文字列定数の末尾にある場合。 以下に例を示します。
a_output := a_output || '' AND name LIKE ''''foobar''''''
実際にa_output
に追加される値は、 AND name LIKE 'foobar'
です。
ドル引用符を用いる時は、次のようになります。
a_output := a_output || $$ AND name LIKE 'foobar'$$
文字列定数内に 2つの単一引用符を持たせたい場合(これで8個の単一引用符になり)、かつ、この文字列定数の末尾にある場合(これで2個追加されます)。 おそらく、他の関数を生成する関数を作成する場合(例 42.10)のみにこれが必要になるでしょう。 以下に例を示します。
a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;'';
a_output
の値は以下のようになります。
if v_... like ''...'' then return ''...''; end if;
ドル引用符を用いる時は、次のようになります。
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;
ここで単一引用符は使用前に再評価されるため、a_output
内部だけで必要であると仮定します。
単純でありふれた問題が有害となる前の実例を発見するユーザを助けるためPL/PgSQLは付加的checks
を提供します。
可能かどうかは設定に依存しますが、関数のコンパイルのときWARNING
またはERROR
を省略して使用できます。
WARNING
を指定された関数は、それ以上のメッセージを生成しないで実行できます。
したがって、分離された開発環境でテストを実行できます。
開発環境やテスト環境では、plpgsql.extra_warnings
やplpgsql.extra_errors
を適切に"all"
に設定することを勧めます。
この付加的チェックでは、設定変数plpgsql.extra_warnings
を警告のためにplpgsql.extra_errors
をエラーのために使用できます。
どちらも、カンマで区切ったチェックリストまたは"none"
または"all"
と設定できます。
デフォルトは"none"
です。
現在指定できるチェックの一覧は以下の通りです。
shadowed_variables
宣言が以前に定義した変数を隠すかどうかチェックする。
strict_multi_assignment
PL/PgSQLコマンドのいくつかは、SELECT INTO
のように、一度に2つ以上の変数に値を割り当てることを許しています。
PL/PgSQLは、ない値に対してはNULL
を使い、余分な変数は無視しますが、通常は対象の変数の数と元の変数の数は一致するべきです。
このチェックを有効にすると、PL/PgSQLは対象の変数の数と元の変数の数が異なる場合には必ずWARNING
またはERROR
を発生するようになります。
too_many_rows
このチェックを有効にすると、PL/PgSQLはINTO
句が使われている場合、与えられた問い合わせが2行以上の行を返すかどうか確認します。
INTO
文は必ず1行に対してのみ使われますので、複数の行を返す問い合わせがあるということは一般に非効率かつ/または非決定論的であり、そのためおそらくエラーです。
以下の例では、plpgsql.extra_warnings
をshadowed_variables
に設定した結果を示します。
SET plpgsql.extra_warnings TO 'shadowed_variables'; CREATE FUNCTION foo(f1 int) RETURNS int AS $$ DECLARE f1 int; BEGIN RETURN f1; END; $$ LANGUAGE plpgsql; WARNING: variable "f1" shadows a previously defined variable LINE 3: f1 int; ^ CREATE FUNCTION
以下の例では、plpgsql.extra_warnings
をstrict_multi_assignment
に設定した結果を示します。
SET plpgsql.extra_warnings TO 'strict_multi_assignment'; CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql AS $$ DECLARE x int; y int; BEGIN SELECT 1 INTO x, y; SELECT 1, 2 INTO x, y; SELECT 1, 2, 3 INTO x, y; END; $$; SELECT foo(); WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. foo ----- (1 row)