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

  • 5 分

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

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

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

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

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

変更できない関数はデータベースを変更できません。呼び出すクエリに関係なく、同じ引数を渡すと同じ結果が返されます。

関数のボラティリティは、クエリ オプティマイザーが処理する効率に大きな違いを生みます。

関数を作成する

関数は 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)、または単一の出力パラメーターと同じ型に一致する必要があります。 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 (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

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

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

部分文字列は、入力テキストの一部を返し、の開始 文字から始まり、文字 カウントした後に停止します。 例えば次が挙げられます。

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

注

関数に慣れている場合、最初のバージョンではコンマではなくキーワードを使用して引数を区切っていることに気付くでしょう。 PostgreSQL には、これらの関数の両方のバージョンが用意されています。