Amazon Redshift 待望の PL/pgSQL のストアドプロシージャをサポートしました

はじめに

ついにPL/pgSQLのストアドプロシージャがサポートされました。これはプロシージャの記述言語にPL/pgSQLが使えるようになり、更にPL/pgSQLを用いてストアドプロシージャが書けるようになるという２つの大きなアップデートを含みます。リリースバージョン 1.0.7562から利用可能になりました。新たに追加になったPL/pgSQLとそのストアドプロシージャについて解説します。

• 最新クラスタにアップデート
• PL/pgSQLとは
• PL/pgSQLの特長
• ストアドプロシージャとは
• ストアドプロシージャの特長
• ストアドプロシージャの例
• ユースケース

最新クラスタにアップデート

クラスタを確認して新しいクラスタ(Cluster Version 1.0.7562 )がスタンバイしている場合は、直ちにアップデートします。

PL/pgSQLとは

PL/pgSQLは、PostgreSQLの手続き型言語PL/pgSQLというSQLクエリとロジックを記述するプログラミング言語です。

PL/pgSQLの特長

PL/pgSQLの特長は以下のとおりです。

• 条件判断（IF-ELSE）やループ（LOOP）などの制御文をサポート
• 動的なSQLの実行（EXECUTE文）をサポート
• カーソルのサポート
• 例外処理のサポート

ストアドプロシージャとは

ストアドプロシージャは、SQLクエリとロジックを登録したものです。データベースに登録したストアドプロシージャは、実行に必要な十分な権限を持つすべてのユーザーがCALLコマンドを使用して実行できます。ストアドプロシージャは、PostgreSQLの手続き型言語PL/pgSQLという言語でSQLクエリとロジックを記述します。

ユーザー定義関数（UDF）とは異なり、ストアドプロシージャはSELECTクエリに加えてデータ定義言語（DDL）およびデータ操作言語（DML）を組み込むことができます。ストアドプロシージャは値を返す必要はありません。ループや条件式などの手続き型言語を使用して、論理フローを制御できます。

ストアドプロシージャの特長

ストアドプロシージャは、一般にデータ変換、データ検証、およびビジネス固有のロジックのカプセル化するために用いられます。複数のSQLステップをストアドプロシージャに結合することで、クライアントアプリケーションとRedshift間のラウンドトリップを減らすことができます。

ストアドプロシージャの例

実際にストアドプロシージャを作成、実行、削除の例です。このストアドプロシージャは、1つのINパラメータ、1つのOUTパラメータ、および1つのINOUTパラメータを持ちます。入力パラメタ（INとINOUT）の入力チェックした後、一時テーブルmy_etlを作成して結果を格納します。ストアドプロシジャの結果は出力パラメータ（OUTとINOUT）の結果を返します。

-- ストアドプロシージャの作成
CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), OUT varchar(256))
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;
CREATE PROCEDURE

-- ストアドプロシージャの実行
CALL test_sp2(2,'2019');
INFO: Table "my_etl" does not exist and will be skipped
f2 | column2
---------------------+---------
2019+2019+2019+2019 | 2
(1 row)

-- ストアドプロシージャの削除
DROP PROCEDURE test_sp2(f1 int, f2 varchar(256));
DROP PROCEDURE

ユースケース

• データ変換やデータ検証におけるクライアントアプリケーションとRedshift間のラウンドトリップの削減
• オブジェクトへの権限を持たないユーザーが、プロシージャの所有者の権限でデータを操作したり、アクセスしたい場合
• 専門的なビジネス固有のロジックのカプセル化
• プロシージャからプロシージャ呼び出しによるモジュール化、プロシージャの再利用

最後に

今回のアップデートはあまりにも範囲が広く、どこまで解説してよいか迷いました。まずはプロシージャの記述言語PL/pgSQLとそのストアドプロシジャがサポートしたということに絞ってお伝えしました。ともに概ねフルスペックの機能が実装されているという印象です。後日、これらについては解説いたします。

合わせて読みたい

Amazon Redshift PL/pgSQLのストアドプロシージャ開発入門