関数の作成 - PolarDB - Alibaba Cloud ドキュメントセンター

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

説明

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

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

既存の関数の現在の定義を置き換えるには、CREATE OR replace functionを使用します。この方法で関数の名前や引数の型を変更することはできません (試した場合、実際には新しい別個の関数を作成することになります) 。また、CREATE OR REPLACE FUNCTIONでは、既存の関数の戻り値の型を変更することはできません。変更するためには、関数を削除して再作成する必要があります。 (OUTパラメーターを使用する場合、関数を削除する以外にOUTパラメーターの型を変更することはできません。)

CREATE OR REPLACE FUNCTIONを使用して既存の関数を置き換える場合、関数の所有権と権限は変更されません。他のすべての関数プロパティには、コマンドで指定または暗黙の値が割り当てられます。これを置き換える関数を所有する必要があります (これには、所有ロールのメンバーであることも含まれます) 。

関数を削除してから再作成すると、新しい関数は古い関数と同じエンティティにはなりません。古い関数を参照する既存のルール、ビュー、トリガーなどを削除する必要があります。関数を参照するオブジェクトを壊すことなく関数定義を変更するには、CREATE OR REPLACE FUNCTIONを使用します。また、ALTER FUNCTIONを使用して、既存の関数のほとんどの補助プロパティを変更できます。

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

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

概要

CREATE [ OR REPLACE ] FUNCTION
        name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ))
        [RETURNS rettype
          | RETURNS TABLE ( column_name column_type [, ...] ) ]
      {言語lang_name
        | TRANSFORM { FOR TYPE type_name } [, ... ]
        | ウィンドウ
        | IMMUTABLE | 安定した | VOLATILE | [ NOT ] LEAKPROOF
        | NULL入力での呼び出し | NULL入力での戻りNULL | STRICT
        | [外部] SECURITY INVOKER | [外部] SECURITY DEFINER
        | PARALLEL { UNSAFE | RESTRICTED | SAFE }
        | COST execution_cost
        | ROWS result_rows
        | support_functionのサポート
        | SET configuration_parameter { TO value | = value | CURRENTから}
        | AS 'definition'
        | 'obj_file' 、'link_symbol' として
      } ...

パラメーター

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

argmode: 引数のモード: IN、OUT、INOUT、またはVARIADIC。省略した場合、デフォルトはINです。 OUT引数のみがVARIADIC引数に続くことができます。また、OUTおよびINOUT引数は、RETURNS TABLE表記と一緒に使用することはできません。

argname: 引数の名前。一部の言語 (SQLおよびPL/pgSQLを含む) では、関数本体で名前を使用できます。他の言語の場合、入力引数の名前は、関数自体に関する限り、単なる追加のドキュメントです。ただし、関数を呼び出すときに入力引数名を使用して、読みやすさを向上させることができます

. いずれの場合も、出力引数の名前は、結果行型の列名を定義するため、重要です。 (出力引数の名前を省略すると、システムはデフォルトの列名を選択します。)

argtype: 関数の引数のデータ型 (必要に応じてスキーマ修飾) (存在する場合) 。引数型は、ベース型、コンポジット型、またはドメイン型、またはテーブル列の型を参照できます。

実装言語によっては、cstringなどの「疑似型」を指定することもできます。疑似型は、実際の引数型が不完全に指定されているか、通常のSQLデータ型のセットの外にあることを示します。

列の型は、table_nameを記述することによって参照されます。column_name % TYPEを使用します。この機能を使用すると、テーブルの定義の変更とは無関係に関数を作成できる場合があります。

default_expr: パラメーターが指定されていない場合にデフォルト値として使用される式。式は、パラメーターの引数型に強制可能でなければなりません。デフォルト値を持つことができるのは、入力 (INOUTを含む) パラメータのみです。デフォルト値を持つパラメータに続くすべての入力パラメータもデフォルト値を持つ必要があります。

rettype: 戻りデータ型 (schema-qualified) 。戻り値の型は、ベース、コンポジット、またはドメインの型にすることも、テーブル列の型を参照することもできます。実装言語によっては、cstringなどの「疑似型」を指定することもできます。関数が値を返さない場合は、戻り値の型としてvoidを指定します。

OUTまたはINOUTパラメーターがある場合、RETURNS句は省略できます。複数の出力パラメータがある場合は、RECORD、または単一の出力パラメータと同じタイプの場合は、出力パラメータによって示される結果タイプと一致する必要があります。

SETOF修飾子は、関数が単一のアイテムではなく、アイテムのセットを返すことを示します。

列の型は、table_nameを記述することによって参照されます。column_name % TYPEを使用します。

column_name: RETURNS TABLE構文の出力列の名前。これは、RETURNS TABLEがRETURNS SETOFも意味することを除いて、名前付きOUTパラメータを宣言する別の方法である。

colum_type: RETURNS TABLE構文の出力列のデータ型。

lang_name: 関数が実装されている言語の名前。 sql、c、internal、またはユーザー定義の手続き型言語の名前 (plpgsqlなど) を使用できます。名前を一重引用符で囲むことは推奨されず、一致するケースが必要です。

TRANSFORM {{ FOR TYPE type_name} [, ... ] }: 関数への呼び出しを変換するリストが適用されます。変換は、SQL型と言語固有のデータ型を変換します。「CREATE TRANSFORM」をご参照ください。手続き型言語の実装には通常、組み込み型のハードコードされた知識があるため、ここにリストする必要はありません。手続き型言語実装で型の処理方法がわからず、変換が提供されていない場合は、データ型を変換するための既定の動作にフォールバックしますが、これは実装によって異なります。

WINDOW: WINDOWは、関数がプレーン関数ではなくウィンドウ関数であることを示します。これは現在、Cで記述された関数にのみ役立ちます。既存の関数定義を置き換えるとき、WINDOW属性は変更できません。

IMMUTABLE STABLE VOLATILE: これらの属性は、関数の動作についてクエリオプティマイザに通知します。最大で1つの選択肢を指定できます。これらのいずれも表示されない場合、VOLATILEがデフォルトの仮定です。

IMMUTABLEは、関数がデータベースを変更できず、同じ引数値が指定された場合に常に同じ結果を返すことを示します。つまり、データベースの検索を行わないか、引数リストに直接存在しない情報を使用しません。このオプションを指定すると、すべて定数の引数を持つ関数の呼び出しは、すぐに関数の値に置き換えることができます。

STABLEは、関数がデータベースを変更できないこと、および1回のテーブルスキャンで同じ引数値に対して同じ結果が常に返されることを示しますが、その結果はSQL文全体で変化する可能性があります。これは、結果がデータベース検索、パラメータ変数 (現在のタイムゾーンなど) などに依存する関数の適切な選択です。(現在のコマンドで変更された行を照会するAFTERトリガーには不適切です。) また、current_timestamp関数ファミリは、トランザクション内で値が変更されないため、安定していると見なされます。

VOLATILEは、関数値が1回のテーブルスキャン内でも変更できるため、最適化を行うことができないことを示します。この意味で揮発性であるデータベース関数は比較的少数です。いくつかの例は、random() 、currval() 、timeofday() です。ただし、副作用のある関数は、結果が非常に予測可能であっても、呼び出しが最適化されないようにするために、揮発性に分類する必要があることに注意してください。例はsetval() です。

LEAKPROOF: LEAKPROOFは、機能に副作用がないことを示します。戻り値以外の引数に関する情報は明らかになりません。たとえば、いくつかの引数値に対してエラーメッセージをスローし、他の引数値に対してはスローしない関数、または任意のエラーメッセージに引数値を含める関数は、漏れ防止ではありません。これは、security_barrierオプションで作成されたビューまたは行レベルのセキュリティが有効なテーブルに対するクエリの実行方法に影響します。システムは、データの不注意な露出を防止するために、ノンリークプルーフ機能を含むクエリ自体からユーザが供給した条件の前に、セキュリティポリシーおよびセキュリティバリアビューからの条件を強制する。漏れ防止としてマークされた機能および演算子は、信頼できると仮定され、セキュリティポリシーおよびセキュリティバリアビューからの条件の前に実行され得る。さらに、引数を取らない関数、またはセキュリティバリアビューまたはテーブルから引数を渡さない関数は、セキュリティ条件の前に実行されるために漏れ防止としてマークされる必要はない。

CALLED ON NULL INPUT RETURNS NULL ON NULL INPUT STRICT: CALLED ON NULL INPUT (デフォルト) は、引数の一部がnullの場合に関数が正常に呼び出されることを示します。必要に応じてnull値をチェックし、適切に応答するのは、関数の作成者の責任です。

RETURNS NULL ON NULL INPUTまたはSTRICTは、引数のいずれかがnullである場合、関数が常にnullを返すことを示します。このパラメーターが指定されている場合、null引数がある場合、関数は実行されません。代わりに、null結果が自動的に仮定されます。

[EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER: SECURITY INVOKERは、関数を呼び出すユーザーの特権で関数が実行されることを示します。これがデフォルトです。 SECURITY DEFINERは、関数を所有するユーザーの権限で関数を実行することを指定します。

キーワードEXTERNALはSQLの適合に使用できますが、SQLとは異なり、この機能は外部の関数だけでなくすべての関数に適用されるため、オプションです。

PARALLEL: PARALLEL UNSAFEは、関数を並列モードで実行できないことを示し、SQLステートメントにそのような関数が存在すると、シリアル実行プランが強制されます。この値がデフォルトです。 PARALLEL RESTRICTEDは、関数を並列モードで実行できるが、実行が並列グループリーダーに制限されることを示します。 PARALLEL SAFEは、関数が制限なしで並列モードで安全に実行できることを示します。

関数は、データベースの状態を変更する場合、またはサブトランザクションを使用するなどのトランザクションに変更を加える場合、またはシーケンスにアクセスするか、設定 (setvalなど) を永続的に変更しようとする場合、並列安全でないとラベル付けする必要があります。一時テーブル、クライアント接続状態、カーソル、準備されたステートメント、またはシステムが並列モードで同期できないその他のバックエンドローカル状態 (たとえば、別のプロセスによって行われた変更がリーダーに反映されないため、setseedをグループリーダー以外で実行できない) にアクセスする場合は、並列制限としてラベル付けする必要があります。一般に、関数が制限または安全でないときに安全であるとラベル付けされている場合、または実際に安全でないときに制限されているとラベル付けされている場合、並列クエリで使用されたときにエラーをスローしたり、誤った回答を生成したりする可能性があります。 C言語関数は、システムが任意のCコードからそれ自体を保護する方法がないため、誤ってラベル付けされた場合、理論的には完全に未定義の動作を示す可能性がありますが、ほとんどの場合、結果は他の関数よりも悪くありません。疑わしい場合、関数はデフォルトであるUNSAFEとしてラベル付けする必要があります。

COST execution_cost: 関数の推定実行コストをcpu_operator_cost単位で示す正の数。関数がセットを返す場合は、これは返される行ごとのコストです。コストが指定されていない場合は, C言語と内部関数に1単位, それ以外のすべての言語の関数に100単位が仮定されます。値が大きいと、プランナーは必要以上に頻繁に関数を評価しないようにします。

ROWS result_rows: 関数が返される予定の推定行数を示す正の数です。これは、関数がセットを返すと宣言されている場合にのみ許可されます。デフォルトの仮定は1000行です。

SUPPORT support_function: この関数に使用するプランナーサポート関数の名前 (スキーマ修飾) 。このオプションを使用するには、スーパーユーザーである必要があります。

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

SET句が関数にアタッチされている場合、同じ変数に対して関数内で実行されるSET LOCALコマンドの効果は関数に制限されます。ただし、通常のSETコマンド (LOCALなし) は、以前のSET LOCALコマンドと同じように、SET句をオーバーライドします。このようなコマンドの効果は、現在のトランザクションがロールバックされない限り、関数の終了後も持続します。

definition: 関数を定義する文字列定数。意味は言語によって異なります。内部関数名、オブジェクトファイルへのパス、SQLコマンド、または手続き型言語のテキストを使用できます。

obj_file、link_symbol: この形式のAS句は、C言語ソースコードの関数名がSQL関数の名前と同じでない場合に、動的にロード可能なC言語関数に使用されます。文字列obj_fileは、コンパイルされたC関数を含む共有ライブラリファイルの名前であり、LOADコマンドの場合と同様に解釈されます。 link_symbolのは、関数のリンクシンボル、つまりC言語のソースコードの関数の名前です。リンク記号が省略されている場合は、定義されているSQL関数の名前と同じであると見なされます。すべての関数のC名は異なる必要があるため、オーバーロードされたC関数に異なるC名を与える必要があります (たとえば、C名の一部として引数型を使用します) 。

CREATE FUNCTION呼び出しが同じオブジェクトファイルを参照する場合、ファイルはセッションごとに1回だけロードされます。ファイルをアンロードしてリロードするには (おそらく開発中に) 、新しいセッションを開始します。

オーバーロード

PolarDBでは、関数のオーバーロードが可能です。つまり、異なる入力引数型がある限り、複数の異なる関数に同じ名前を使用できます。

2つの関数は、OUTパラメーターを無視して、同じ名前と入力引数型を持つ場合、同じと見なされます。したがって、たとえばこれらの宣言は競合します。

CREATE FUNCTION foo(int) ...
    CREATE FUNCTION foo(int、out text) ...

異なる引数型リストを持つ関数は、作成時に競合するとは見なされませんが、デフォルトが指定されている場合、使用中に競合する可能性があります。たとえば、

CREATE FUNCTION foo(int) ...
    CREATE FUNCTION foo(int、int default 42)...

foo(10) の呼び出しは、どの関数を呼び出すべきかについてのあいまいさのために失敗します。

注

fullSQLtype構文は、関数の引数と戻り値の宣言に使用できます。ただし、括弧内の型修飾子 (たとえば、型numericの精度フィールド) はCREATE FUNCTIONによって破棄されます。したがって、たとえばCREATE FUNCTION foo (varchar(10)) ...とまったく同じです関数foo (varchar) を作成します....

既存の関数をCREATE OR REPLACE functionに置き換える場合、パラメーター名の変更に制限があります。入力パラメーターに既に割り当てられている名前を変更することはできません (ただし、これまでになかったパラメーターに名前を追加することはできます) 。複数の出力パラメーターがある場合、出力パラメーターの名前を変更することはできません。これは、関数の結果を記述する匿名複合型の列名が変更されるためです。これらの制限は、関数の既存の呼び出しが置き換えられても機能を停止しないようにするために行われます。

関数がVARIADIC引数でSTRICTと宣言されている場合、厳密性チェックでは、全体としてのvariadic配列のがnull以外であることがテストされます。配列にnull要素がある場合、関数は引き続き呼び出されます。

例

SQL関数を使用して2つの整数を追加します。

CREATE FUNCTION add(integer, integer) return integer
        AS 'select $1 + $2;'
        言語SQL
        IMMUTABLE
        NULL入力にNULLを返します。

整数をインクリメントし、引数名inPL/pgSQLを使用します。

CREATE OR REPLACE FUNCTION increment(i integer) $$$として整数を返します
            BEGIN
                    リターンi + 1;
            END;
    $$言語plpgsql;

複数の出力パラメータを含むレコードを返します。

CREATE FUNCTION dup (int、out f1 int、out f2テキスト)
        AS $$ SELECT $1, CAST($1 AS text) | | 'is text' $$$$
        言語SQL;

    SELECT * FROM dup(42);

明示的に名前が付けられた複合型でも、同じことをより冗長に行うことができます。

CREATE TYPE dup_result AS (f1 int、f2テキスト);

    CREATE FUNCTION dup(int) RETURNS dup_result
        AS $$ SELECT $1, CAST($1 AS text) | | 'is text' $$$$
        言語SQL;

    SELECT * FROM dup(42);

複数の列を返す別の方法は、TABLE関数を使用することです。

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
        AS $$ SELECT $1, CAST($1 AS text) | | 'is text' $$$$
        言語SQL;

    SELECT * FROM dup(42);

ただし、TABLE関数は、実際には1つのレコードだけでなく、レコードのセットを返すため、上記の例とは異なります。

安全に`セキュリティDEFINER`関数を記述する

SECURITY DEFINER関数は、それを所有するユーザーの権限で実行されるため、関数が悪用されないように注意する必要があります。セキュリティのために、信頼されていないユーザーが書き込み可能なスキーマを除外するようにsearch_pathを設定する必要があります。これは、悪意のあるユーザが、関数によって使用されることが意図されるオブジェクトをマスクするオブジェクト (例えば、テーブル、関数、および演算子) を作成することを防止する。この点で特に重要なのは、デフォルトで最初に検索され、通常は誰でも書き込み可能な一時テーブルスキーマです。一時スキーマを最後に検索するように強制することによって、安全な配置を得ることができる。これを行うには、search_pathの最後のエントリとしてpg_tempを記述します。この関数は安全な使用法を示します:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
    $$としてBOOLEANを返す
    DECLAREはBOOLEANに合格しました。
    BEGIN
            SELECT (pwd = $2) INTOが渡されました
            pwdsから
            WHEREユーザー名= $1;

            リターン渡される;
    END;
    $$言語plpgsql
        セキュリティ擁護者
        -安全なsearch_path: 信頼できるスキーマを設定し、次に「pg_temp」を設定します。
        SET search_path = admin, pg_temp;

この関数の目的は、テーブルadmin.pwdsにアクセスすることです。ただし、SET句がない場合、またはSET句でadminのみが指定されている場合、pwdsという名前の一時テーブルを作成することで関数が破壊される可能性があります。

覚えておくべきもう1つのポイントは、デフォルトで、新しく作成された関数のPUBLICに実行権限が付与されることです。多くの場合、セキュリティ定義関数の使用を一部のユーザーのみに制限したいと思うでしょう。これを行うには、デフォルトのPUBLIC権限を取り消してから、実行権限を選択的に付与する必要があります。すべての人が新しい関数にアクセスできるウィンドウを持たないようにするには、それを作成し、単一のトランザクション内で特権を設定します。設定例：

BEGIN;
    CREATE FUNCTION check_password(uname TEXT、pass TEXT) ... セキュリティ擁護者;
    PUBLICから機能check_password(uname TEXT、pass TEXT) のすべてを更新します。
    GRANT EXECUTE ON FUNCTION check_password(uname TEXT、pass TEXT) 管理者へ。
    コミット;

説明

概要

パラメーター

オーバーロード

注

例

安全にセキュリティDEFINER関数を記述する

安全に`セキュリティDEFINER`関数を記述する