メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012年12月1日)

CREATE FUNCTION

SQL SELECT 句または Python プログラムを使用して、新しいスカラーユーザー定義関数 (UDF) を作成します。

構文

Copy
CREATE [ OR REPLACE ] FUNCTION f_function_name ( { [py_arg_name py_arg_data_type | sql_arg_data_type } [ , ... ] ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ { python_program | SELECT_clause } $$ LANGUAGE { plpythonu | sql }

Parameters

OR REPLACE

その関数がすでに存在するの関数と同じ名前と入力引数のデータタイプ、あるいは署名である場合、既存の関数を置き換えることを指定します。 同一のデータタイプセットを定義する新しい関数によってのみ既存の関数を置き換えることができます。 関数の置き換えは、スーパーユーザーのみが行うことができます。

すでに存在するの関数と同じ名前と入力引数のデータタイプで、異なる署名の関数を定義する場合は、新しい関数を作成することになります。 つまり、関数名はオーバーロードされます。 詳細については、「関数名の多重定義」を参照してください。

f_function_name

関数の名前。 スキーマ名を指定すると (myschema.myfunction など)、指定したスキーマを使用して関数が作成されます。 指定しない場合、現在のスキーマに関数が作成されます。 有効な名前の詳細については、「名前と識別子」を参照してください。

f_ をすべての UDF 名のプレフィックスとすることをお勧めします。 Amazon Redshift は UDF 名のプレフィックス f_ をリザーブしているので、f_ プレフィックスを使用すると、その UDF 名が既存また今後のすべての Amazon Redshift ビルトイン SQL の関数名とも競合しないことが保証されます。詳細については、「UDF の命名」を参照してください。

入力引数のデータタイプが異なる場合、同じ関数名で 1 つ以上の関数を定義することができます。 つまり、関数名はオーバーロードされます。 詳細については、「関数名の多重定義」を参照してください。

py_arg_name py_arg_data_type | sql_arg_data type

Python UDF の場合、入力引数の名前とデータタイプの一覧。SQL UDF の場合、引数名のないデータタイプの一覧。Python UDF の場合は、引数名を使用して引数を参照します。SQL UDF では、引数の一覧での引数の順序に基づいて、$1、$2 などを使用して引数を参照します。

SQL UDF の場合、入力および戻りデータタイプは、どの標準 Amazon Redshift データタイプでも可能です。Python UDF の場合、入力および戻りデータタイプは、TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) を除く任意の標準 Amazon Redshift データタイプにすることができます。さらに、Python UDF は ANYELEMENT のデータタイプをサポートします。このタイプは、実行時に示される対応する引数のデータタイプに基づく標準データタイプに自動的に変換されます。複数の引数が ANYELEMENT を使用している場合には、一覧の最初の ANYELEMENT の引数に基づき、実行時に同じデータタイプに変換されます。 詳細については、「Python UDF データ型」および「データ型」を参照してください。

最大 32 の引数を指定できます。

RETURNS data_type

データタイプの値は関数によって返されます。 RETURNS データタイプは、どの標準 Amazon Redshift データタイプでも可能です。また、Python UDF は ANYELEMENT データタイプを使用できます。このタイプは、実行時に示される引数に基づく標準データタイプに自動的に変換されます。戻り型のデータタイプとして ANYELEMENT を指定する場合、少なくとも 1 つの ANYELEMENT 引数を使用する必要があります。 実際の戻り型のデータタイプは、関数が呼び出された場合に ANYELEMENT 引数から提供されるデータタイプと一致することになります。 詳細については、「Python UDF データ型」を参照してください。

VOLATILE | STABLE | IMMUTABLE

関数の変動率についてのクエリオプティマイザを報告します。

関数の最適な変動率の分類を厳正に設定することで、最高の最適化が得られます。 ただし、変動幅が厳正すぎると、オプティマイザはその呼び出しを誤って省略してしまい、よって不正確な結果セットを報告することになってしまいます。厳正度の順に、低度の厳正度から変動率を分類すると:

  • 変動性

  • 安定

  • 不変

変動性

同じ引数が入っている場合、単一のステートメントに含まれる行であっても、関数は連続の呼び出しに異なる結果を返すことがあります。 クエリオプティマイザは変動的な関数のどのような動作についても仮定条件を作成しないので、変動性の関数を使用するクエリは、各入力行につき、関数の再評価が行われるべきです。

安定

同じ引数が入っている場合、関数が単一のステートメント内で処理されるすべての行に対して同じ結果を返すことが保証されます。 異なるステートメントから呼び出された場合、関数が異なる結果を返すことがあります。 この分類は、単一のステートメント内でのステートメントへの 1 回の呼び出しにおける関数の複数の呼び出しを最適化するオプティマイザを可能にします。

不変

同じ引数が入っている場合、関数は常に永遠に同じ結果を返します。 クエリが定数引数の IMMUTABLE 関数を呼び出すと、オプティマイザは関数を前評価します。

AS $$ ステートメント $$

実行するステートメントを囲む構造。リテラルキーワードが AS $$$$ であることが必須です。

Amazon Redshift では、dollar quoting という形式を使ったステートメントを関数に含める必要があります。 含まれるすべてがそのまま移行されます。 文字列の内容はそのまま書き込まれるため、どのような特殊文字も外す必要はありません。

dollar quoting では、2 つのドル記号 ($$) を使用して実行するステートメントの開始と終了を示します。使用例は次の通りです。

Copy
$$ my statement $$

任意で、それぞれのドル記号ペアの間に文字列を指定して、ステートメントの特定に役立たせることもできます。 使用できる文字列は、エンクロージャペアの開始と終了の両方において同じである必要があります。 この文字列では大文字と小文字が区別され、ドル記号が含まれたもの以外は、引用符で囲まれていない識別子と同じ規約条件に従います。 次の例では、test、 を使用します

Copy
$test$ my statement $test$

dollar quoting に関する詳細については、PostgreSQL マニュアルの「Lexical Structure」セクションで、Dollar-quoted String Constants を参照してください。

python_program

値を返す有効で実行可能な Python プログラム。 関数と共に渡すステートメントは、Python ウェブサイトの Style Guide for Python Code に示されるインデント要件に準拠する必要があります。 詳細については、「UDF のための Python 言語のサポート」を参照してください。

SQL_clause

SQL SELECT 句。

SELECT 句には、以下のタイプの句を含めることはできません。

  • FROM

  • INTO

  • WHERE

  • GROUP BY

  • ORDER BY

  • 制限

言語 { plpythonu | sql }

Python の場合は、plpythonu を指定します。SQL の場合は、sql を指定します。SQL または plpythonu 用の言語で使用のアクセス権限を持っている必要があります。詳細については、「UDF のセキュリティおよび権限」を参照してください。

使用に関する注意事項

ネストされた関数

SQL UDF 内から別の SQL UDF を呼び出すことができます。CREATE FUNCTION コマンドを実行するときは、ネストされた関数が存在している必要があります。Amazon Redshift は UDF の依存関係の追跡しないため、ネストされた関数を削除すると、Amazon Redshift はエラーを返しません。ただし、ネストされた関数が存在しない場合、UDF は失敗します。たとえば、次の関数は SELECT 句で f_sql_greater 関数を呼び出します。

Copy
create function f_sql_commission (float, float ) returns float stable as $$ select f_sql_greater ($1, $2) $$ language sql;

UDF のセキュリティおよび権限

UDF を作成するには、SQL または plpythonu (Python) 用の言語で使用のアクセス権限を持っている必要があります。デフォルトでは、USAGE ON LANGUAGE SQL が PUBLIC に付与されますが、特定のユーザーまたはグループに明示的に USAGE ON LANGUAGE PLPYTHONU を付与する必要があります。

SQL の使用を取り消すには、最初に PUBLIC から使用を取り消します。次に、SQL UDF を作成するために許可された特定のユーザーまたはグループにのみ、SQL での使用を許可します。次の例では、PUBLIC から SQL での使用を取り消し、ユーザーグループ udf_devs に使用を許可します。

Copy
revoke usage on language sql from PUBLIC; grant usage on language sql to group udf_devs;

UDF を実行するには、関数ごとに実行権限を持っている必要があります。デフォルトでは、新しい UDF を実行する権限が PUBLIC に付与されます。使用を制限するには、関数に対して PUBLIC から実行を取り消します。次に、特定の個人またはグループに権限を付与します。

次の例では、PUBLIC から関数 f_py_greater の実行を取り消し、ユーザーグループ udf_devs に使用を許可します。

Copy
revoke execute on function f_py_greater(a float, b float) from PUBLIC; grant execute on function f_py_greater(a float, b float) to group udf_devs;

スーパーユーザーは、デフォルトですべての権限を持っています。

詳細については、「GRANT」および「REVOKE」を参照してください。

スカラー Python UDF の例

次の例は、2 つの整数を比較し、大きいほうの数値を返す Python UDF を作成する方法を示しています。

Copy
create function f_py_greater (a float, b float) returns float stable as $$ if a > b: return a return b $$ language plpythonu;

次の例は、SALES テーブルを検索して新しい f_py_greater 関数を呼び出すことによって、COMMISSION または PRICEPAID の 20% のどちらか大きいほうを返します。

Copy
select f_py_greater (commission, pricepaid*0.20) from sales;

スカラー SQL UDF の例

次の例は、2 つの数値を比較し、大きいほうの数値を返す関数を作成する方法を示しています。

Copy
create function f_sql_greater (float, float) returns float stable as $$ select case when $1 > $2 then $1 else $2 end $$ language sql;

次のクエリは、新しい f_sql_greater 関数を呼び出して SALES テーブルをクエリし、COMMISSION または PRICEPAID の 20% のどちらか大きいほうを返します。

Copy
select f_sql_greater (commission, pricepaid*0.20) from sales;