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

石川覚

2019.05.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

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

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

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