Amazon Redshift
データベース開発者ガイド

Amazon Redshift でのストアドプロシージャの概要

ストアドプロシージャは、通常、データ変換やデータ検証のロジック、ビジネス固有のロジックをカプセル化するために使用します。複数の SQL ステップをストアドプロシージャにまとめることで、アプリケーションとデータベースを往復する回数を減らすことができます。

細かいアクセスコントロールを行う場合、ストアドプロシージャを作成して、基礎となるテーブルへのアクセスをユーザーに許可することなく関数を実行できます。たとえば、所有者またはスーパーユーザーのみがテーブルを切り詰めることができます。また、ユーザーがテーブルにデータを挿入する場合は書き込みアクセス許可が必要です。基となるテーブルへのアクセス許可をユーザーに付与することなく、タスクを実行するストアドプロシージャを作成できます。次に、このストアドプロシージャを実行するためのアクセス許可をユーザーに付与します。

ストアドプロシージャは、DEFINER セキュリティ属性が設定されている場合、ストアドプロシージャの所有者の権限で実行されます。デフォルトでは、ストアドプロシージャには INVOKER セキュリティが設定されます。この場合、プロシージャはプロシージャを呼び出したユーザーのアクセス許可を使用します。

ストアドプロシージャを作成するには、CREATE PROCEDURE コマンドを使用します。プロシージャを実行するには、CALL コマンドを使用します。このセクションで後ほど例を紹介します。

注記

一部のクライアントは、Amazon Redshift のストアドプロシージャの作成時に次のエラーをスローする場合があります。

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

このエラーは、セミコロン区切りのステートメントとドル記号 ($) 引用符を使用して CREATE PROCEDURE ステートメントをクライアントが正しく解析できないために発生します。これにより、ステートメントの一部のみ Amazon Redshift サーバーに送信されます。通常、このエラーは、クライアントの Run as batch オプションまたは Execute selected オプションを使用することで回避できます。

たとえば、Aginity クライアントを使用する場合は、Run entire script as batch オプションを使用します。SQL Workbench/J を使用する場合は、バージョン 124 をお勧めします。SQL Workbench/J バージョン 125 を使用する場合は、回避策として別の区切り文字を指定することを検討してください。CREATE PROCEDURE にはセミコロン (;) で区切られた SQL ステートメントが含まれているため、スラッシュ (/) など、別の区切り文字を定義し、CREATE PROCEDURE ステートメントの最後に配置すると、ステートメント全体が処理のために Amazon Redshift サーバーに送信されます。以下に例を示します。

CREATE OR REPLACE PROCEDURE test() AS $$ BEGIN SELECT 1 a; END; $$ LANGUAGE plpgsql ; /

詳細については、SQL Workbench/J ドキュメントの「別の区切り文字」を参照してください。または、Amazon Redshiftコンソールのクエリエディタや TablePlus など、CREATE PROCEDURE ステートメントの解析のサポートが強化されたクライアントを使用します。

次の例は、出力引数を使用しないプロシージャを示しています。デフォルトでは、引数は入力 (IN) 引数です。

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar) AS $$ BEGIN RAISE INFO 'f1 = %, f2 = %', f1, f2; END; $$ LANGUAGE plpgsql; call test_sp1(5, 'abc'); INFO: f1 = 5, f2 = abc CALL

次の例は、出力引数を使用するプロシージャを示しています。引数は、入力t (IN)、入力および出力 (INOUT)、出力 (OUT) です。

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; call test_sp2(2,'2019'); f2 | column2 ---------------------+--------- 2019+2019+2019+2019 | 2 (1 row)