★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

42.12. PL/pgSQLによる開発向けのヒント

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がこうしたツールの一例です。 こうしたツールは、単一引用符をエスケープさせたり、関数の作り直しやデバッグが容易に行えたりする便利な機能をよく持っています。

42.12.1. 引用符の扱い

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$のような記述を使用できます。

以下の表はドル引用符を用いない時の引用符の記述法を示したものです。 ドル引用符を用いる以前における引用符の記述を理解するのに、この表は役立つと思われます。

1つの引用符

関数本体の先頭と末尾。 以下に例を示します。

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

関数本体内部では引用符は必ずペアで現れます。

2つの引用符

関数本体内部の文字列リテラル用。 以下に例を示します。

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

ドル引用符を用いる時は、次のように記述します。

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

どちらもPL/pgSQLパーサから見ると同一となります。

4つの引用符

関数本体内部の文字列リテラル内の単一引用符がある場合。 以下に例を示します。

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$$

なお、ドル引用符の区切り文字は$$だけとは限らないことに注意してください。

6つの引用符

関数本体内部の文字列内の単一引用符が、文字列定数の末尾にある場合。 以下に例を示します。

a_output := a_output || '' AND name LIKE ''''foobar''''''

実際にa_outputに追加される値は、 AND name LIKE 'foobar'です。

ドル引用符を用いる時は、次のようになります。

a_output := a_output || $$ AND name LIKE 'foobar'$$

10個の引用符

文字列定数内に 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内部だけで必要であると仮定します。

42.12.2. コンパイル時と実行時の付加的チェック

単純でありふれた問題が有害となる前の実例を発見するユーザを助けるためPL/PgSQLは付加的checksを提供します。 可能かどうかは設定に依存しますが、関数のコンパイルのときWARNINGまたはERRORを省略して使用できます。 WARNINGを指定された関数は、それ以上のメッセージを生成しないで実行できます。 したがって、分離された開発環境でテストを実行できます。

開発環境やテスト環境では、plpgsql.extra_warningsplpgsql.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/PgSQLINTO句が使われている場合、与えられた問い合わせが2行以上の行を返すかどうか確認します。 INTO文は必ず1行に対してのみ使われますので、複数の行を返す問い合わせがあるということは一般に非効率かつ/または非決定論的であり、そのためおそらくエラーです。

以下の例では、plpgsql.extra_warningsshadowed_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_warningsstrict_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)