PL/pgSQLを使ってみよう

本記事は[PostgreSQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/postgresql)の10日目となります。
昨日は @ikkitang さんの [RDSにあるデータをDMSでEC2に移して対応してないプラグインを利用する](https://www.ikkitang1211.site/entry/postgresql-advent-9) というお話でした。

PL/pgSQLでストアドファンクションを作成するのにあたり最低限知っておいて欲しいことをまとめてみました。

# ストアドファンクションとは？
ストアドファンクションとは、データベースに対する一連の処理をまとめてデータベースに登録したものとなります。
データベース内で処理が完結し、クライアント/サーバー通信のオーバーヘッドを減らすことが可能となります。
PostgreSQLでストアドファンクションを作成する場合、PL/pgSQLやPL/Python、PL/Perlといった言語で作成します。
PostgreSQL11から、ストアドファンクションと似ているストアドプロシージャも作成することができるようになりました。
ストアドファンクションは一つの処理をまとめたもので、戻り値がないものと認識してもらえれば良いです。(正確には処理結果を受け取ることが可能です。)
このエントリではストアドファンクションのみ解説を行い、ストアドファンクションのことをストアドと省略します。

# PL/pgSQLとは？
PL/pgSQLは、SQLを手続き型言語として拡張したプログラミング言語です。
公式ドキュメントに記載されている設計目的は、以下の通りです。

- 関数とトリガを作成するために使用できること
- SQL言語に制御構造を追加すること
- 複雑な演算が可能であること
- 全てのユーザ定義型、関数、演算子を継承すること
- サーバによって信頼できるものと定義できること
- 使いやすいこと

デフォルトでPL/pgSQLは有効化されているため、通常はそのまま使用することができます。
OracleのPL/SQLと似たような言語となるため、移植性があります。

# 初めてのストアド作成
文字(名前)を引数とし、そのまま返却するストアドを作成してみましょう。

```sql
create or replace function test(in name text)
returns text as $$
  declare res text := name;
begin
  return res;
end;
$$ language plpgsql;
```

無事に作成できたら、実行してみましょう。

```sql
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 <名前>.`としてみましょう。

```sql
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;
```

無事に更新できたら、実行してみましょう。

```sql
postgres=# select test('ester41');
    test
-------------
 Hi ester41.
(1 row)

postgres=#
```

## コードの解説
#### 5行目: `select 'Hi ' || res || '.' into res;`
select句で文字列を接続し、結果を`res`に代入しています。
クエリ中に変数を使用することが可能で、反対に変数に代入する場合は`into`を使用します。

## ストアドの削除
`drop function test(text);`を実行することで、`test`を削除することができます。

# テーブルの問い合わせ結果を使用する
ストアドは、複数レコードを返すクエリを使用して処理することが可能です。
まずはテストストアドを作成してみましょう。

```sql
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;
```

無事に作成できたら、テスト用テーブルを作成してストアドを実行してみましょう。

```sql
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点を試してみましょう。

```sql
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;
```

無事に更新できたら、実行してみましょう。

```sql
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エラーコード](https://www.postgresql.jp/document/current/html/errcodes-appendix.html)に存在します。
#### 11行目～12行目: `raise exception 'ストアド内でエラーが発生しました。' using detail = 'パラメーター: ' || table_name;`
`raise`文で、メッセージを出力することができます。
今回指定した`exception`レベルは、即座にトランザクションを失敗させる効果を持ちます。
そのほかのレベルは、ログを出力するのみです。
詳しくは、[エラーとメッセージ](https://www.postgresql.jp/document/current/html/plpgsql-errors-and-messages.html)を参照してください。

# もっと詳しく知るためには
PL/pgSQLにはもっとたくさんの機能が存在します。
使いこなせれば複雑なことも実現できます。
PostgreSQLの公式ドキュメントを参照することで、詳しく知ることができます。
[PL/pgSQL - SQL手続き言語](https://www.postgresql.jp/document/current/html/plpgsql.html)

# 最後に
PL/pgSQLは簡単でありながら、奥が深い言語です。
ただし、多用すると処理系がバラバラとなりコードを追いかけることが難しくなります。
できればビジネスロジックはストアド化することは避けるべきでしょう。
何度も登場する処理やプロジェクト内で統一したい処理といった物の中でデータベースで完結する物のみ、ストアドの採用を検討するようにしましょう。