本記事はPostgreSQL Advent Calendar 2019の10日目となります。
昨日は @ikkitang さんの RDSにあるデータをDMSでEC2に移して対応してないプラグインを利用する というお話でした。
PL/pgSQLでストアドファンクションを作成するのにあたり最低限知っておいて欲しいことをまとめてみました。
ストアドファンクションとは？
ストアドファンクションとは、データベースに対する一連の処理をまとめてデータベースに登録したものとなります。
データベース内で処理が完結し、クライアント/サーバー通信のオーバーヘッドを減らすことが可能となります。
PostgreSQLでストアドファンクションを作成する場合、PL/pgSQLやPL/Python、PL/Perlといった言語で作成します。
PostgreSQL11から、ストアドファンクションと似ているストアドプロシージャも作成することができるようになりました。
ストアドファンクションは一つの処理をまとめたもので、戻り値がないものと認識してもらえれば良いです。(正確には処理結果を受け取ることが可能です。)
このエントリではストアドファンクションのみ解説を行い、ストアドファンクションのことをストアドと省略します。
PL/pgSQLとは？
PL/pgSQLは、SQLを手続き型言語として拡張したプログラミング言語です。
公式ドキュメントに記載されている設計目的は、以下の通りです。
- 関数とトリガを作成するために使用できること
- SQL言語に制御構造を追加すること
- 複雑な演算が可能であること
- 全てのユーザ定義型、関数、演算子を継承すること
- サーバによって信頼できるものと定義できること
- 使いやすいこと
デフォルトでPL/pgSQLは有効化されているため、通常はそのまま使用することができます。
OracleのPL/SQLと似たような言語となるため、移植性があります。
初めてのストアド作成
文字(名前)を引数とし、そのまま返却するストアドを作成してみましょう。
create or replace function test(in name text)
returns text as $$
declare res text := name;
begin
return res;
end;
$$ language plpgsql;
無事に作成できたら、実行してみましょう。
postgres=# select test('ester41');
test
---------
ester41
(1 row)
postgres=#
コードの解説
1行目: create or replace function test(in name text)
testという名前のストアドを作成します。
カッコの中は、一つのtext型引数を受け取ることを意味します。
or replaceを付けていることで、引数や戻り値の型を変えない限りストアドを更新できるようになります。
2行目: returns text as $$
戻り値がtext型であることを指定しています。
as $$は、ストアドの始点を意味します。
3行目: declare res text := name;
変数宣言です。
text型で resを作成し、引数のnameを代入しています。
:=が代入するという意味です。
4行目: begin
ストアドの処理開始を意味します。
5行目: return res;
ストアドの戻り値に引数resを指定しています。
6行目: end;
ストアドの処理終了を意味します。
7行目: $$ language plpgsql;
ストアドの終点と、このストアドがPL/pgSQLで記述されていることを指定します。
ストアドの改造
では、ストアドを少し改造して、戻り値をHi <名前>.としてみましょう。
create or replace function test(in name text)
returns text as $$
declare res text := name;
begin
select 'Hi ' || res || '.' into res;
return res;
end;
$$ language plpgsql;
無事に更新できたら、実行してみましょう。
postgres=# select test('ester41');
test
-------------
Hi ester41.
(1 row)
postgres=#
コードの解説
5行目: select 'Hi ' || res || '.' into res;
select句で文字列を接続し、結果をresに代入しています。
クエリ中に変数を使用することが可能で、反対に変数に代入する場合はintoを使用します。
ストアドの削除
drop function test(text);を実行することで、testを削除することができます。
テーブルの問い合わせ結果を使用する
ストアドは、複数レコードを返すクエリを使用して処理することが可能です。
まずはテストストアドを作成してみましょう。
create or replace function out_column_name(in table_name text)
returns table(
id integer
, name text
) as $$
declare rec record;
begin
for rec in
select
pa.attnum
, pa.attname
from pg_catalog.pg_attribute pa
inner join pg_catalog.pg_class pc
on pa.attrelid = pc.oid
inner join pg_catalog.pg_namespace pn
on pn.oid = pc.relnamespace
where
pn.nspname = current_schema()
and pc.relname = table_name
and pa.attnum > 0
order by pa.attnum
loop
id := rec.attnum;
name := rec.attname;
return next;
end loop;
return;
end;
$$ language plpgsql;
無事に作成できたら、テスト用テーブルを作成してストアドを実行してみましょう。
postgres=# create table test_table(
postgres(# col1 int
postgres(# , col2 int
postgres(# );
CREATE TABLE
postgres=# select * from out_column_name('test_table');
id | name
----+------
1 | col1
2 | col2
(2 rows)
postgres=#
コードの解説
3行〜6行目: returns table(...)
テーブル形式の戻り値を指定しています。
7行目: declare rec record;
forループで使用するrecord型のrecを作成しています。
PostgreSQLも他RDBMSと同様にカーソルを使用できますが、forループを使用することで自動的に使用され、ユーザーは意識する必要はありません。
9行〜26行目: for rec in クエリ loop ループ内処理 end loop;
クエリを実行し結果をrec変数に代入を行い、なくなるまでループ内処理を実行し続けます。
ループ処理処理では、recに格納されたレコードにアクセスすることができます。
25行目: return next;
戻り値のテーブルのレコードを一つ進めています。
27行目: return;
戻り値は初期化時に設定しているため、ここでは変数を指定していません。
ストアド内で生成したクエリの実行と例外処理
ストアドは、動的に生成したクエリを実行することができます。
動的にクエリを発行するということは、失敗(例外)もあり得るということです。
この2点を試してみましょう。
create or replace function dynamic_query(in table_name text)
returns integer as $$
declare res text := 1;
declare create_sql text;
begin
create_sql := 'select distinct 1 from ' || table_name;
execute create_sql;
return res;
exception
when undefined_table then
raise exception 'ストアド内でエラーが発生しました。'
using detail = 'パラメーター: ' || table_name;
end;
$$ language plpgsql;
無事に更新できたら、実行してみましょう。
postgres=# select dynamic_query('pg_catalog.pg_user');
dynamic_query
---------------
1
(1 行)
postgres=# select dynamic_query('dummy');
ERROR: ストアド内でエラーが発生しました。
DETAIL: パラメーター: dummy
CONTEXT: PL/pgSQL function dynamic_query(text) line 10 at RAISE
postgres=#
コードの解説
6行目: create_sql := 'select distinct 1 from ' || table_name;
実行したいクエリを作成しています。
7行目: execute create_sql;
作成したクエリを実行しています。
クエリの実行結果を受け取る方法や、プレースホルダーで実行することも可能です。
9行目: exception
例外処理を開始することを意味します。
10行目: when undefined_table then
例外の一つである、テーブルが存在しない場合のエラーのみ処理するようにしています。
エラーコードの一覧は、付録 A. PostgreSQLエラーコードに存在します。
11行目～12行目: raise exception 'ストアド内でエラーが発生しました。' using detail = 'パラメーター: ' || table_name;
raise文で、メッセージを出力することができます。
今回指定したexceptionレベルは、即座にトランザクションを失敗させる効果を持ちます。
そのほかのレベルは、ログを出力するのみです。
詳しくは、エラーとメッセージを参照してください。
もっと詳しく知るためには
PL/pgSQLにはもっとたくさんの機能が存在します。
使いこなせれば複雑なことも実現できます。
PostgreSQLの公式ドキュメントを参照することで、詳しく知ることができます。
PL/pgSQL - SQL手続き言語
最後に
PL/pgSQLは簡単でありながら、奥が深い言語です。
ただし、多用すると処理系がバラバラとなりコードを追いかけることが難しくなります。
できればビジネスロジックはストアド化することは避けるべきでしょう。
何度も登場する処理やプロジェクト内で統一したい処理といった物の中でデータベースで完結する物のみ、ストアドの採用を検討するようにしましょう。