`CREATE FUNCTION`

CREATE FUNCTION は新しい関数を定義します。

重要

Amazon Aurora DSQL では、CREATE FUNCTION は SQL 言語関数 (LANGUAGE sql) のみをサポートしています。

サポートされている構文


CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE sql
    | { IMMUTABLE | STABLE | VOLATILE }
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | RETURN expression
    | BEGIN ATOMIC statement; [ statement; ... ] END
  } ...

where LANGUAGE must be sql

説明

CREATE FUNCTION は新しい関数を定義します。CREATE OR REPLACE FUNCTION は新しい関数を作成するか、既存の定義を置き換えます。関数を定義できるためには、その言語に対する USAGE 権限を持っている必要があります。

スキーマ名が含まれている場合、関数は指定されたスキーマに作成されます。含まれていない場合、関数は現在のスキーマに作成されます。新しい関数の名前は、同じスキーマ内にある、入力引数の型が同じ既存の関数の名前と重複してはなりません。ただし、引数の型が異なる関数であれば、同一の名前を共有できます (これはオーバーロードと呼ばれます)。

既存の関数に関する現在の定義を置き換えるには、CREATE OR REPLACE FUNCTION を使用します。この方法で関数の名前や引数の型を変更することはできません (これにより、実際には別の新しい関数が作成されます)。また、CREATE OR REPLACE FUNCTION を使用して既存の関数に関する戻り値の型を変更することはできません。変更するには、関数を削除して再作成する必要があります。

CREATE OR REPLACE FUNCTION を使用して既存の関数を置き換えても、関数の所有権とアクセス許可は変更されません。その他すべての関数プロパティには、コマンドで指定された値または暗黙的に示された値が割り当てられます。関数を置き換えるには、その関数の所有者でなければなりません (これには、所有ロールのメンバーであることも含まれます)。

関数を削除して再作成する場合、新しい関数は古い関数と同じエンティティではありません。古い関数を参照する既存のルールやビューなどを削除する必要があります。関数を参照するオブジェクトを壊さずに関数定義を変更するには、CREATE OR REPLACE FUNCTION を使用します。

関数を作成したユーザーが、関数の所有者になります。

関数を作成できるためには、引数の型と戻り値の型に対する USAGE 権限が必要です。

SQL 関数は、1 つ以上の SQL ステートメントを再利用可能かつ呼び出し可能なユニットにまためたものです。関数を使用して、よく使う式や計算を 1 箇所にまとめ、複数のクエリをまたいで一貫したロジックを適用し、同じ SQL を繰り返さずに 1 つの関数を呼び出すことでアプリケーションコードを簡素化します。

パラメータ

name

作成する関数の名前 (スキーマ修飾名も可)。

argmode

引数のモード: IN、OUT、INOUT、または VARIADIC。省略した場合、デフォルトは IN です。VARIADIC は最後の入力パラメータである必要があります。この後には OUT 引数のみを続けることができます。OUT 引数と INOUT 引数を RETURNS TABLE 表記と組み合わせることはできません。

argname

引数の名前。SQL 関数では、関数本文で引数を名前で参照できます。OUT 引数または INOUT 引数の名前も重要です。結果の行タイプで対応する列名を定義するためです。

argtype

引数のデータ型。これは、Aurora DSQL がサポートする任意の基本データ型、配列型、またはドメインのいずれかです。詳細については、「Aurora DSQL でサポートされているデータ型」を参照してください。%TYPE 表記を使用して、既存のテーブル列のタイプを参照することもできます。

default_expr

引数が指定されていない場合にデフォルト値として使用される式。式は、パラメータの引数の型に変換可能である必要があります。IN 引数と INOUT 引数のみがデフォルト値を持つことができます。デフォルト値を持つ引数の後に続くすべての引数にも、デフォルト値が必要です。

rettype

戻り値のデータ型。これは、Aurora DSQL がサポートする任意の基本データ型、配列型、またはドメインのいずれかです。詳細については、「Aurora DSQL でサポートされているデータ型」を参照してください。行のセットを返すには、SETOF rettype または RETURNS TABLE を使用します。関数が値を返さない場合は、void を戻り値の型として指定します。OUT パラメータまたは INOUT パラメータがある場合、RETURNS 句は省略でき、関数は record または対応する行タイプを返します。

RETURNS TABLE ( column_name column_type [, ...] )

関数が行のセットとともに名前付き列とそのタイプを返すよう指定します。これは、1 つ以上の OUT パラメータを宣言し、SETOF を戻り値の型として指定することと同じです。

LANGUAGE sql

関数の実装に使用される言語。Aurora DSQL では、これは sql である必要があります。LANGUAGE 句を省略して BEGIN ATOMIC 本文または RETURN 本文を指定すると、sql が想定されます。文字列定数本文 (AS 'definition') には明示的な LANGUAGE sql 句が必要です。これを省略すると、エラー no language specified で失敗します。

IMMUTABLE / STABLE / VOLATILE

これらの属性は、クエリオプティマイザに関数の動作を知らせます。最大で 1 つを指定できます。これら 3 つをすべて省略すると、関数はデフォルトで VOLATILE になります。

IMMUTABLE は、関数がデータベースを変更できないこと、そして同じ引数値を指定したときに常に同じ結果を返すことを示します。
STABLE は、関数がデータベースを変更できないことを示します。単一のテーブルスキャン内では、同じ引数値に対して一貫して同じ結果を返しますが、SQL ステートメント間では結果が変わる可能性があります。
VOLATILE は、単一のテーブルスキャン内でも関数値が変わる可能性があるため、最適化を実行できないことを示します。

CALLED ON NULL INPUT / RETURNS NULL ON NULL INPUT / STRICT

CALLED ON NULL INPUT (デフォルト) は、引数の一部が null の場合でも関数が正常に呼び出されることを示します。RETURNS NULL ON NULL INPUT または STRICT は、引数のいずれかが null の場合に、関数が常に null を返すことを示します。指定した場合、引数のいずれかが null の場合、Aurora DSQL は関数を実行しません。代わりに、関数は自動的に null を返します。

[ EXTERNAL ] SECURITY INVOKER / [ EXTERNAL ] SECURITY DEFINER

SECURITY INVOKER (デフォルト) は、関数を呼び出すユーザーの権限で関数を実行することを示します。SECURITY DEFINER は、関数を所有するユーザーの権限で関数を実行することを指定します。キーワード EXTERNAL は SQL 準拠のために許可されていますが、省略可能です。

PARALLEL { UNSAFE | RESTRICTED | SAFE }

Aurora DSQL は PARALLEL マーキングを受け入れて保存します。ただし、このマーキングはクエリの実行には影響しません。

COST execution_cost

関数の推定実行コストを示す正の数。関数がセットを返す場合、この値は、返される行あたりの推定コストです。クエリプランナーは、関数をインライン化しない場合に、この推定値を使用します。

ROWS result_rows

クエリプランナーが、関数から返されると想定する推定行数を示す正の数。これは、関数がセットを返すように宣言されている場合にのみ許可されます。デフォルトの想定数は 1000 行です。

SET configuration_parameter { TO value | = value | FROM CURRENT }

SET 句を使用すると、指定された設定パラメータは、関数の入力時に指定された値に設定され、関数の終了時に以前の値に復元されます。SET FROM CURRENT は、CREATE FUNCTION の実行時に有効になっているパラメータの値を、関数の入力時に適用される値として保存します。

AS 'definition'

関数を定義する文字列定数。意味は言語によって異なります。SQL 言語関数の場合は、関数本文を構成する SQL ステートメントです。

RETURN expression

単一の式を返す関数の SQL 標準構文を使用して関数本文を定義します。これは文字列定数形式に代わるものです。

BEGIN ATOMIC statement; [ ... ] END

SQL 標準構文を使用して関数本文を定義します。本文は 1 つ以上の SQL ステートメントで構成されます。このフォームは LANGUAGE sql に対してのみ有効です。アトミック本文内のステートメントは、関数の依存関係として追跡されます。例えば、本文で参照されているテーブルを削除するには、一緒に関数を削除するか、DROP ... CASCADE を使用する必要があります。

注記

DROP FUNCTION ステートメントを使用して関数を削除します。また、GRANT および REVOKE (REVOKE ALL ON FUNCTION など) を使用して、関数に対する権限を付与したり、取り消したりすることもできます。

テーブル内のデータの読み取りや変更を行うすべての関数は、直接実行される同等の SQL ステートメントと同じトランザクションおよび並行処理の動作に従います。IMMUTABLE または STABLE として宣言された関数は、データベースを変更してはなりません。

既存の関数の定義を更新するには、CREATE OR REPLACE FUNCTION を使用します。この方法では関数の引数の型や戻り値の型を変更できません。変更する場合は、関数を削除して再作成します。

関数名の変更、所有者の変更、別のスキーマへの移動を行うには、ALTER FUNCTION を使用します。その他の属性を変更するには、CREATE OR REPLACE FUNCTION を使用し、必要な変更点を指定します。詳細については、「ALTER FUNCTION」を参照してください。

関数のオーバーロードがサポートされています。入力引数の型が異なれば、同じ名前の関数を複数定義できます。ユーザー定義関数は他の関数を呼び出したり、名前と引数の型が同じである組み込み関数を上書きしたりすることもできます。

本文に複数のステートメントが含まれているセット (SETOF または RETURNS TABLE) を返す関数の場合、最後のステートメントの結果のみが返されます。関数はそれより前のステートメントを引き続き実行しますが、その結果は破棄されます。これは SQL 言語関数の標準的な動作です。最後のステートメントが完全な結果セットを生成するよう関数を設計してください。

セットを返す関数は、特定の行順序を保証しません。行順序を制御するには、クエリに ORDER BY 句を含めます。

考慮事項

以下の CREATE FUNCTION 機能は、表示されたエラーを返します。

LEAKPROOF: エラー only superuser can define a leakproof function を返します。NOT LEAKPROOF (デフォルト) は受け入れられます。
TRANSFORM FOR TYPE: エラー transform for type type language "sql" does not exist を返します。
SUPPORT (プランナーサポート関数): エラー function name(internal) does not exist を返します。
複合型を返す関数を含む、ユーザー定義の複合型 (CREATE TYPE ... AS): エラー unsupported statement: CompositeType を返します。

Aurora DSQL は CREATE PROCEDURE をサポートしていません。これを使用すると、エラー PROCEDURE is not supported で失敗します。

WINDOW 属性は構文的には受け入れられますが、SQL 言語関数には役立ちません。この属性の使用は避けてください。

例

文字列定数の本文を使用して、2 つの整数を追加するシンプルな SQL 関数を作成します。


CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'SELECT $1 + $2;'
    LANGUAGE sql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

SQL 標準の RETURN 本文と名前付き引数を使用して、同じ関数を作成します。


CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE sql
    IMMUTABLE
    RETURN a + b;

RETURNS TABLE と BEGIN ATOMIC 本文を使用して一連の行を返す関数を作成します。


CREATE FUNCTION employees_in_dept(dept_id integer)
    RETURNS TABLE (id integer, name text)
    LANGUAGE sql
    STABLE
    BEGIN ATOMIC
        SELECT id, name FROM employees WHERE department_id = dept_id;
    END;

所有者の権限で実行され、呼び出し中に設定パラメータを設定する関数を作成します。


CREATE FUNCTION current_department() RETURNS text
    LANGUAGE sql
    STABLE
    SECURITY DEFINER
    SET search_path = app
    AS 'SELECT current_setting(''app.department'');';

互換性

CREATE FUNCTION コマンドは SQL 標準で定義されています。PostgreSQL 実装は互換性のある方法で使用できますが、多くの拡張機能があります。逆に、SQL 標準は PostgreSQL に実装されていない多くのオプション機能を規定しています。

以下は、互換性に関する重要な問題です。

OR REPLACE は PostgreSQL の拡張機能です。
他のデータベースシステムと互換性を確保するために、argmode は argname の前後どちらにも記述できます。ただし、標準に準拠しているのは前への記述のみです。
パラメータのデフォルトの場合、SQL 標準は DEFAULT キーワードを使用した構文のみを規定しています。= を使用した構文は、T-SQL および Firebird で使用されます。
SETOF 修飾子は PostgreSQL の拡張機能です。
SQL のみが言語として標準化されています。
CALLED ON NULL INPUT と RETURNS NULL ON NULL INPUT を除く他のすべての属性は標準化されていません。
LANGUAGE sql 関数の本文の場合、SQL 標準は sql_body フォームのみを規定しています。

シンプルな LANGUAGE sql 関数は、標準に準拠するとともに、他の実装にも移植可能な方法で記述できます。高度な機能、最適化属性、または他の言語を使う、より複雑な関数は、必然的にその大部分が PostgreSQL 固有のものとなります。

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

SET CONSTRAINTS

ALTER FUNCTION