Amazon Redshift
データベース開発者ガイド (API バージョン 2012-12-01)

CREATE PROCEDURE

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

構文

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name ( [ [ argname ] [ argmode ] argtype [, ...] ] ) 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」を参照してください。

SECURITY INVOKER | SECURITY DEFINER

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

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

SECURITY DEFINER モードの場合、プロシージャはプロシージャの所有者としてのデータベース特権を使用して実行されます。プロシージャを呼び出すユーザーは、プロシージャに対する実行権限を必要としますが、基礎となるオブジェクトに対する権限は不要です。

SET configuration_parameter { TO value | = value }

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

次の例では、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;

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

CREATE OR REPLACE PROCEDURE test_sp2(f1 int, f2 INOUT varchar, OUT varchar) AS $$ DECLARE out_var alias for $3; 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;

このページの内容: