CREATE FUNCTION - Amazon Redshift
必要な権限構文パラメータ使用に関する注意事項例

CREATE FUNCTION

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

詳細な説明と例については、「Amazon Redshift のユーザー定義関数」を参照してください。

必要な権限

CREATE OR REPLACE FUNCTION を実行するには、以下のいずれかの方法によるアクセス許可が必要です。

  • CREATE FUNCTION の場合:

    • スーパーユーザーは、関数を作成する際に、信頼された言語と信頼できない言語の両方を使用できます。

    • CREATE [または REPLACE] FUNCTION の権限を持つユーザーは、信頼できる言語による関数の作成が行えます。

  • REPLACE FUNCTION の場合:

    • スーパーユーザー

    • CREATE [または REPLACE ] FUNCTION の権限を持つユーザー

    • 関数の所有者

構文

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 }

パラメータ

OR REPLACE

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

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

f_function_name

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

すべての UDF 名の前に f_ を付けることをお勧めします。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 では、入力データ型および戻りデータ型に SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE、または TIMESTAMP を使用できます。さらに、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

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

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

  • VOLATILE

  • 安定

  • 不変

変動性

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

安定

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

不変

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

AS $$ statement $$

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

Amazon Redshift では、関数のステートメントをドル引用符という形式を使用して囲む必要があります。囲まれた内容がそのまま渡されます。文字列の内容がそのまま書き込まれるため、特殊文字のエスケープは一切不要です。

ドル引用符付けでは、次の例に示すように、実行するステートメントの開始と終了を 2 つのドル記号のペア ($$) で指定します。

$$ my statement $$

必要に応じて、各ペアのドル記号間にステートメントの識別に役立つ文字列を指定できます。使用する文字列は、開始と終了の囲い文字のペアで同じにする必要があります。この文字列では大文字と小文字が区別され、ドル記号を含めることができない点を除いては、引用符で囲まれていない識別子と同じ制約事項に従います。次の例では、文字列 test を使用しています 

$test$ my statement $test$

ドル引用符付けの詳細については、PostgreSQL ドキュメントの「Lexical StructureDollar」の「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 関数を呼び出します。

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 に使用を許可します。

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 に使用を許可しています。

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 を作成する方法を示しています。

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% のどちらか大きいほうを返します。

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

スカラー SQL UDF の例

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

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% のどちらか大きいほうを返します。

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