Postgresに保存されているExcelで処理できないこともない量のテーブルデータをヘッダと明細に分ける処理を行う必要に駆られたのですが、折角なのでストアドを利用することにしました。
感想は、OracleのPL/SQLにとても似ていて、基本的な内容についてはほぼ同じように感じで意外とさくっと進みました。
遙か昔、PL/SQLをごりごりと書いていた頃には、MySQLやPostgresのストアドは不安定だから云々と聞いていたが、今回使用してみた限りそれほど悪いものではないように感じました。 （といってもとても簡単なものを書いただけなので、複雑なバッチ処理などはどうかわかりませんが…）
PostgresのストアドプロシージャーはPL/pgSQLと呼ばれていて、基本的な内容は下記に詳しく説明があります。
基本的な機能として、Oracleのストアドプロシージャーとストアドファンクション、トリガーは機能として提供されているようです。今回使用したストアドプロシージャーも実態はストアドファンクションでありストアドプロシージャーらしい記述はありませんでした。
本当にPL/SQLとにたシンタックスなので、Oracleを経験したことがある人はあまり躓くこともないと思いますが、さっくりとどんな感じかだけまとめたいと思います。
[bash]
CREATE OR REPLACE FUNCTION convert_ledger()RETURNS text AS $$ DECLARE -- set bound cursor table_cursor cursor FOR SELECT ... FROM table_a; table_rec table_a%ROWTYPE; table_id table_a."id"%TYPE; var_str varchar(100); BEGIN
RAISE NOTICE '>> 処理の開始';
open table_rec;
LOOP raise notice '>>> loop in'; FETCH table_cursor into table_rec; EXIT WHEN NOT FOUND;
raise notice '>>> data is exists!';
-- get table_id select nextval('table_seq') into table_id;
insert into table_b(col_a,col_b,col_c) valuesl(table_rec.aaa , table_rec.bbb , …);
END LOOP;
close table_cursor;
RETURN '正常終了';
EXCEPTION WHEN OTHERS THEN
select SQLSTATE || ':' || SQLERRM INTO var_str; return var_str; END; $$ LANGUAGE PLpgSQL;
[/bash]
とまぁ、こんな感じでカーソルの宣言やFETCH、取得したデータを列定義型の変数として参照するなど簡単にできます。トリガなどで履歴をとることもできそうですし、結構期待できそうな感じです。
と、ここまで書いて気づいたのですが、OracleのPL/SQLと違うところがありました。それはトランザクションの扱いです。PL/pgSQLの場合には、Begin〜End句は実質的な処理単位をまとめるための表記であり、いわゆる文法上のトランザクション単位ではありません。したがって、Begin〜End中にCommitを入れようがRollbackを入れようが影響ないと言うことになります。ストアドを実行した外部プログラム側から制御されたトランザクションに基本的に依存することになるためです。
ただし、Exception句においては例外的にBegin〜Endにおいて実施された処理がRollbackされます。例外が発生して、適切に補足された場合には処理をRollbackするようになっているようです。ということなので、ストアドを実行したプログラムではRollback は行う必要がないと言うことになります…。少し調べてみないといけないですね。
なお、公式ドキュメント以外ではこちらの書籍がとても役に立ちます。（Postgresについてはこの書籍がバイブル的な存在ですね。）
