CREATE PROCEDURE

新しいストアドプロシージャを作成するか、現在のデータベースの既存のプロシージャを置き換えます。

詳細な説明と例については、「Amazon Redshift のストアドプロシージャの作成」を参照してください。

必要な権限

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

CREATE PROCEDURE の場合:
- スーパーユーザー
- ストアドプロシージャの作成先のスキーマに対する CREATE 権限と USAGE 権限を持つユーザー
REPLACE PROCEDURE の場合:
- スーパーユーザー
- プロシージャの所有者

構文


CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

パラメータ

OR REPLACE

プロシージャの名前と入力引数のデータタイプ (署名) が既存のプロシージャと同じである場合、既存のプロシージャを置き換えることを指定する句。プロシージャは、同じデータタイプセットを定義する新しいプロシージャにのみ置き換えることができます。

既存のプロシージャと名前は同じでも、署名が異なるプロシージャを定義する場合は、新しいプロシージャを作成することになります。つまり、プロシージャ名は重複されます。詳細については、「プロシージャ名の多重定義」を参照してください。

sp_procedure_name

プロシージャの名前。スキーマ名 (myschema.myprocedure など) を指定すると、プロシージャは指定したスキーマで作成されます。指定しない場合、プロシージャは現在のスキーマで作成されます。有効な名前の詳細については、「名前と識別子」を参照してください。

すべてのストアドプロシージャ名の前に sp_ を付けることをお勧めします。Amazon Redshift は、ストアドプロシージャ名用に sp_ プレフィックスを予約します。sp_ プレフィックスを使用すると、ストアドプロシージャ名は既存または将来の Amazon Redshift の組み込みストアドプロシージャ名や関数名と競合しません。詳細については、「ストアドプロシージャの名前付け」を参照してください。

入力引数のデータタイプ (署名) が異なる場合、同じ名前で複数のプロシージャを定義できます。つまり、この場合、プロシージャ名は重複されます。詳細については、「プロシージャ名の多重定義」を参照してください。

[argname] [ argmode] argtype

引数の名前、モード、およびデータタイプのリスト。データタイプのみが必須です。名前とモードはオプションであり、両者の位置は交換できます。

引数のモードは IN、OUT、INOUT のいずれかです。デフォルトは IN です。

OUT 引数および INOUT 引数を使用して、プロシージャ呼び出しから 1 つ以上の値を返すことができます。OUT 引数または INOUT 引数がある場合、プロシージャ呼び出しは、n 列が含まれている 1 つの結果行を返します。n は OUT 引数または INOUT 引数の合計数です。

INOUT 引数は同時に入力引数であり、出力引数です。入力引数には IN 引数と INOUT 引数の両方が含まれます。出力引数には OUT 引数と INOUT 引数の両方が含まれます。

OUT 引数は CALL ステートメントの一部として指定されません。INOUT 引数は、ストアドプロシージャの CALL ステートメントに指定します。INOUT 引数は、ネストされた呼び出しとの間で値を受け渡すときと、refcursorを返すときに役立つ場合があります。refcursor タイプの詳細については、「カーソル」を参照してください。

引数のデータタイプには、Amazon Redshift のすべての標準データタイプを使用できます。さらに、引数のデータタイプとして refcursor も使用できます。

最大 32 個の入力引数と最大 32 個の出力引数を指定できます。

AS $$ procedure_body $$

実行するプロシージャを囲む構造。リテラルキーワードの AS $$ および $$ は必須です。

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

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


$$ my statement $$

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


$test$ my statement $test$

この構文は、ネストされたドル引用符付けにも役立ちます。ドル引用符付けの詳細については、PostgreSQL ドキュメントの「Lexical Structure」で「Dollar-quoted String Constants」を参照してください。

procedure_body

有効な PL/pgSQL ステートメントのセット。PL/pgSQL ステートメントは、SQL コマンドをプロシージャ構造体 (ループや条件式など) で強化し、論理フローを制御します。大半の SQL コマンドはプロシージャ本文で使用できます。これには、COPY、UNLOAD、INSERT などのデータ変更言語 (DML) と CREATE TABLE などのデータ定義言語 (DDL) が含まれます。詳細については、「PL/pgSQL 言語リファレンス」を参照してください。

LANGUAGE plpgsql

言語の値。plpgsql を指定します。plpgsql を使用するために言語の使用に関するアクセス許可が必要です。詳細については、「GRANT」を参照してください。

NONATOMIC

ストアドプロシージャを非アトミックトランザクションモードで作成します。NONATOMIC モードは、プロシージャ内のステートメントを自動的にコミットします。また、NONATOMIC プロシージャ内でエラーが発生しても、例外ブロックによって処理される場合、エラーは再スローされません。詳細については、「トランザクションの管理」および「RAISE」を参照してください。

ストアドプロシージャを NONATOMIC として定義する場合は、次の点を考慮してください。

ストアドプロシージャコールをネストするときには、すべてのプロシージャを同じトランザクションモードで作成する必要があります。
NONATOMIC モードでプロシージャを作成する場合、SECURITY DEFINER オプションと SET configuration_parameter オプションはサポートされません。
すべての (明示的または暗黙的に) 開いているカーソルは、暗黙的なコミットが処理されるときに自動的に閉じられます。そのため、カーソルループを開始する前に明示的なトランザクションを開いて、ループの反復処理内の SQL が暗黙的にコミットされないようにする必要があります。

SECURITY INVOKER | SECURITY DEFINER

SECURITY DEFINER オプションは、NONATOMIC が指定されている場合はサポートされません。

プロシージャのセキュリティモードは、実行時のプロシージャのアクセス権限を決定します。プロシージャは、基礎となるデータベースオブジェクトにアクセスするためのアクセス許可を必要とします。

SECURITY INVOKER モードの場合、プロシージャはプロシージャを呼び出すユーザーの特権を使用します。ユーザーは、基礎となるデータベースオブジェクトに対する明示的なアクセス許可を必要とします。デフォルトは SECURITY INVOKER です。

SECURITY DEFINER モードの場合、このプロシージャは、プロシージャ所有者の権限を使用します。プロシージャ所有者の定義は、実行時にプロシージャを所有するユーザーであり、必ずしも最初にプロシージャを定義したユーザーではありません。プロシージャを呼び出すユーザーは、プロシージャに対する実行権限を必要としますが、基礎となるオブジェクトに対する権限は不要です。

SET configuration_parameter { TO value | = value }

これらのオプションは、NONATOMIC が指定されている場合はサポートされません。

SET 句は、プロシージャの開始時に、指定した configuration_parameter を指定した値に設定します。次に、プロシージャの終了時に、この句は configuration_parameter を以前の値に戻します。

使用に関する注意事項

SECURITY DEFINER オプションを使用してストアドプロシージャが作成された場合、そのストアドプロシージャ内から CURRENT_USER 関数を呼び出すと、Amazon Redshift はストアドプロシージャの所有者のユーザー名を返します。

例

注記

このような例を実行した場合、次のようなエラーが発生します。


ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

「Amazon Redshift でのストアドプロシージャの概要」を参照してください。

次の例では、2 つの入力パラメータを使用するプロシージャを作成します。


CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

注記

ストアドプロシージャを記述する場合は、機密の値を保護するためのベストプラクティスに従うことをお勧めします。

ストアドプロシージャロジックに機密情報をハードコーディングしないでください。例えば、ストアドプロシージャの本文の CREATE USER ステートメントにユーザーパスワードを割り当てないでください。ハードコードした値は、カタログテーブルにスキーマメタデータとして記録される可能性があるため、セキュリティ上のリスクが生じます。代わりに、パスワードなどの機密の値は、パラメータを使用して引数として、ストアドプロシージャに渡します。

ストアドプロシージャの詳細については、「CREATE PROCEDURE」と「Amazon Redshift のストアドプロシージャの作成」を参照してください。カタログテーブルの詳細については、「システムカタログテーブル」を参照してください。

次の例では、1 つの IN パラメータ、1 つの OUT パラメータ、および 1 つの INOUT パラメータを使用するプロシージャを作成します。


CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;

次の例では、SECURITY DEFINER パラメータを使用するプロシージャを作成します。このプロシージャは、プロシージャを所有するユーザーの権限を使用して実行します。


CREATE OR REPLACE PROCEDURE sp_get_current_user_definer()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

次の例では、SECURITY INVOKER パラメータを使用するプロシージャを作成します。この手順は、プロシージャを実行するユーザーの権限を使用して実行します。


CREATE OR REPLACE PROCEDURE sp_get_current_user_invoker()
AS $$
DECLARE curr_user varchar(250);
BEGIN
  SELECT current_user INTO curr_user;
  RAISE INFO '%', curr_user;
END;
$$ LANGUAGE plpgsql
SECURITY INVOKER;

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

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

ドキュメントの表記規則

ユースケース

CREATE RLS POLICY