Azure Database for PostgreSQL で関数を作成して使用する

• 5 分

PostgreSQL がさまざまな言語をサポートしていることを既に学習しました。 関数は、次の 4 種類に分類できます。

• SQL で記述された関数。
• PL.pgSQL などのサポートされている手続き型言語で記述された手続き型言語関数。
• 内部関数。
• C 言語関数。

さらに、関数の目的も、揮発性、不変、または安定として分類できます。

揮発性 (既定) 関数はデータベースを変更することができ、毎回同じ入力パラメーターを指定しても同じ結果を返すとは限りません。 そのため、この関数を呼び出すたびに、評価し直す必要があります。

安定関数はデータベースを変更することができず、同じ引数を渡して同じステートメント内で実行すると同じ結果を返します。 この関数を複数回呼び出した場合、クエリ オプティマイザーは最後に呼び出されたときの結果を使用できます。

不変関数はデータベースを変更することができず、同じ引数を渡すと、それを呼び出しているクエリに関係なく同じ結果を返します。

関数の揮発性により、クエリ オプティマイザーがそれを処理する効率に大きな違いがあります。

関数を作成する

関数は 1 つの値を返し、SELECT ステートメント内で使用できます。

関数を作成する構文は次のとおりです。

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

ストアド プロシージャと同様に、文字列を開始および終了するには $$ という記号を使います。

関数は次のパラメーターを受け取ります。

• name - 必要に応じてスキーマ名を含めます。
• argmode - 引数のモード。 IN、OUT、INOUT、または VARIADIC を指定できます。 既定値は IN です。 VARDIAC は、未定義の数の同じ型の入力引数を示し、"その後に OUT 引数が続きます"。 OUT 引数と INOUT 引数を RETURNS TABLE 表記と共に使うことはできません。
• argname - 引数名。
• argtype - 引数のデータ型。 基本型、複合型、またはドメイン型を使ったり、テーブル列型を参照したりできます。 列の型は、table_name.column_name%TYPE と記述されます。 このデータ型は、関数をテーブルの定義の変更と無関係にするのに役立ちます。
• t_expr - パラメーターが指定されていない場合の (同じ型の) 既定値。 既定値を持つのは、IN パラメーターと INOUT パラメーターのみです。 既定値を持つパラメーターに続く入力パラメーターにも、既定値が必要です。
• rettype - 戻り値のデータ型。基本型、複合型、またはドメイン型を使ったり、テーブル列型を参照したりできます。 関数が値を返さない場合は、戻り値の型を void として指定します。 OUT または INOUT パラメーターがある場合は、RETURNS 句を省略できます。 存在する場合は、出力パラメーターによって暗黙的に指定される結果の型 (複数の出力パラメーターがある場合は RECORD、出力パラメーターが 1 つの場合はそれと同じ型) と一致する必要があります。 SETOF 修飾子は、関数が 1 つの項目ではなく、項目のセットを返すことを示します。 列の型を参照するには、table_name を記述します。
• column_name - RETURNS TABLE 構文での出力列の名前。 このパラメーターは名前付きの OUT パラメーターを宣言しますが、RETURNS TABLE が RETURNS SETOF を意味するのは同じです。
• column_type - RETURNS TABLE 構文での出力列のデータ型。
• lang_name - プロシージャの記述に使われる言語。 sql_body が指定されている場合、既定値は sql です。 sql、c、internal、またはユーザー定義の手続き型言語の名前 (plpgsql など) を指定できます。

関数に関するクエリ オプティマイザーへのヒントとしては、キーワード IMMUTABLE、STABLE、または VOLATILE を使います。 VOLATILE が既定値です。

関数を呼び出す

関連するパラメーターを渡すことによって、クエリで関数を使用できます。 次に例を示します。

SELECT myfunction(3), CatID, CatName
    FROM myCats

組み込み関数

PostgreSQL には、クエリで使用できる組み込み関数が多数含まれています。 比較、データの集計、数学関数などに関するものがあります。PostgreSQL 関数の完全な一覧についてのオンライン ドキュメントを参照してください。

substring は、組み込み文字列関数の例です。

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

この関数は、3 つの入力パラメーターを受け取ります。

• 文字列 (テキスト型)
• FROM 開始位置 (型整数)
• FOR 個数 (整数型)

substring は、入力テキストの、開始位置の文字から始まって個数だけ後の文字で終わる部分を返します。 次に例を示します。

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

この関数は substr と同じです。

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph