Amazon Redshift でのストアドプロシージャの概要 - 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 にはセミコロン (;) で区切られた 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

注記

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

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

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

次の例は、出力引数を使用するプロシージャを示しています。引数は、入力 (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)