PostgreSQLで関数を作って、使う[PL/pgSQL入門]

PostgreSQLで使える、関数の作り方を簡単にまとめました。
詳しいリファレンスはWebを探せばいくらでもありますが。。。
ちょっと書きたいだけなのに、なかなか簡単なサンプルが見つからない。。。
ということで、学習しながら試したことをまとめてみます。
手軽なものであれば、サンプルを書き換えれば大丈夫です。

でも、せっかくなのでチュートリアル風にしてみました。

動作確認は`psql (PostgreSQL) 9.6.3`(> psql --version)で行っています。
言語はSQLとPL/pgSQLを使います。

# 準備
関数を実行して試すとき、簡単なテーブルがあると便利です。
そこで、私が使ったものをどうぞ。
次のSQLを保存して実行します。
PostgreSQLならば、データベースに接続して、`\i test.sql`で実行できます。

```sql:test.sql
DROP TABLE IF EXISTS programming;

CREATE TABLE programming(
  id SERIAL,
  name VARCHAR(255) NOT NULL,
  first_appeared INT NOT NULL
);

INSERT INTO programming(name, first_appeared) VALUES
  ('Lisp', 1958),
  ('C', 1972),
  ('SQL', 1974),
  ('python', 1991),
  ('Java', 1995),
  ('PHP', 1995),
  ('Scala', 2004),
  ('Rust', 2010);
```

すると、次のようなテーブルができます。

 id |  name  | first_appeared
----+--------+----------------
  1 | Lisp   |           1958
  2 | C      |           1972
  3 | SQL    |           1974
  4 | python |           1991
  5 | Java   |           1995
  6 | PHP    |           1995
  7 | Scala  |           2004
  8 | Rust   |           2010

これで、この記事内のサンプルを試すことができます。

# SQLによる関数
簡単な処理なら、SQLで関数を作れます。
ちょっと冗長なになりますが、SQLで書けます。

## 関数の作成
ここでは例として、何年前に登場したプログラミング言語かを表示してみることにしましょう。

つまり、このような表を作ります。

  name  | first_appeared | years_ago
--------+----------------+-----------
 Lisp   |           1958 |        59
 C      |           1972 |        45
 SQL    |           1974 |        43
 python |           1991 |        26
 Java   |           1995 |        22
 PHP    |           1995 |        22
 Scala  |           2004 |        13
 Rust   |           2010 |         7

`id`を引数にとって、現在の年から`first_appeared`の値を引いたものを返す関数があればいいですね。
その関数はこうなります。

```plpgsql:years_ago.sql
DROP FUNCTION IF EXISTS years_ago(INTEGER);

CREATE OR REPLACE FUNCTION years_ago(INTEGER)
RETURNS INTEGER AS $$
  SELECT (extract(year from current_date)::INTEGER - first_appeared)
  FROM programming
  WHERE id = $1;
$$ LANGUAGE sql;
```
ここでは`years_ago()`という`INTEGER`を引数にとる関数を定義しています。
この関数の戻り値は`INTEGER`です。(`RETURNS INTEGER`で戻り値の型を指定します。)
本体は`$$ ～ $$`の中です。
引数を使いたいときは`$`に引数の番号を指定します。ここでは先頭の引数を使いたいので、`$1`です。

それ以外は普通のSQLです。

以下は補足です。

- `DROP FUNCTION IF EXISTS years_ago(INTEGER);`で関数を`DROP`しているのは、スムーズに書き換えるためです。PostgreSQLだと、引数を変えたときなど、`CREATE OF REPLACE FUNCTION`でも更新できない場合があります。その際のめんどうを避けるために、まず関数を`DROP`しています。
- `LANGUAGE sql`で、言語を指定します。ここではSQLを使ったので、`sql`です。

## 関数の実行

では、このyears_ago()関数を実行してみます。
SELECTに追加してみましょう。

```sql:
pltest=# SELECT name, first_appeared, years_ago(id) FROM programming;
  name  | first_appeared | years_ago
--------+----------------+-----------
 Lisp   |           1958 |        59
 C      |           1972 |        45
 SQL    |           1974 |        43
 python |           1991 |        26
 Java   |           1995 |        22
 PHP    |           1995 |        22
 Scala  |           2004 |        13
 Rust   |           2010 |         7
```
`years_ago()`は`id`を引数にとるように定義したので、`years_ago(id)`と呼び出しています。

# PL/pgSQLによる関数
PostgreSQLではPL/pgSQLという言語も使えます。
制御文を使った関数を作ることができます。
また、SQL版に比べてプログラムが短くなることもあります。
https://ja.wikipedia.org/wiki/PL/SQL

ドキュメントはこちら
https://www.postgresql.jp/document/9.2/html/plpgsql.html

## 関数の作成
例によって、まずは何年前に登場したプログラミング言語か求める関数を作ります。
でも、先ほどとはちょっと考えを変えます。
`id`ではなくて、`first_appeared`を引数にとってみます。

```plpgsql:years_ago
DROP FUNCTION IF EXISTS years_ago(INTEGER);

CREATE OR REPLACE FUNCTION years_ago(first_appeared INTEGER)
RETURNS INTEGER AS $$
  BEGIN
    RETURN (extract(year from current_date)::INTEGER - first_appeared);
  END;
$$ LANGUAGE plpgsql;
```
PL/pgSQL版のyears_ago()は、引数に名前付きの変数が使えます。可読性が上がります。
また、今回は`PL/pgSQL`なので、`LANGUAGE`は`plpgsql`です。

コードの通り、`PL/pgSQL`では、`BEGIN　～　END;`の間に処理を書きます。

<pre>BEGIN
  処理
END;
</pre>

戻り値は`RETURN`で指定します。

## 関数の実行
では、`years_ago()`関数を使ってみます。先ほどと同じようにSELECTに追加します。

```sql:
pltest=# SELECT name, first_appeared, years_ago(first_appeared) FROM programming;
  name  | first_appeared | years_ago
--------+----------------+-----------
 Lisp   |           1958 |        59
 C      |           1972 |        45
 SQL    |           1974 |        43
 python |           1991 |        26
 Java   |           1995 |        22
 PHP    |           1995 |        22
 Scala  |           2004 |        13
 Rust   |           2010 |         7
```

今回の`years_ago()`関数は`first_appeared`の値を引数にとるように定義しました。
なので、`years_ago(first_appeared)`で呼び出します。

## 変数
`PL/pgSQL`では変数を使うことができます。
また、例によって`years_ago()`を書き換えます。

```plpgsql:years_ago
DROP FUNCTION IF EXISTS years_ago(INTEGER);

CREATE OR REPLACE FUNCTION years_ago(first_appeared INTEGER)
RETURNS INTEGER AS $$
  DECLARE
    current_year INTEGER := extract(year from current_date)::INTEGER;
  BEGIN
    RETURN current_year - first_appeared;
  END;
$$ LANGUAGE plpgsql;
```

コードの通り、`DECLARE`の中に変数を宣言します。
書式は次のとおりです。

```
変数名　型名　:= 値;
```
プログラムが読みやすくなります。

## 制御文
`PL/pgSQL`では制御文が使えます。

### 条件分岐
`PL/pgSQL`では`IF～ELSEIF～ELSE`が使えます。
では、次の関数を作ってみます。

first_appeared < 1990のとき = ancient
1990 <= first_appeared < 2000 のとき = middle
上記にあてはならないとき = modern

`first_appeared`を引数にとり、時代を返す`era()`関数を作ってみます。

次のような表を作作ります。

 id |  name  | first_appeared |   era
----+--------+----------------+---------
  1 | Lisp   |           1958 | ancient
  2 | C      |           1972 | ancient
  3 | SQL    |           1974 | ancient
  4 | python |           1991 | middle
  5 | Java   |           1995 | middle
  6 | PHP    |           1995 | middle
  7 | Scala  |           2004 | modern
  8 | Rust   |           2010 | modern

プログラムは次の通りです。

```plpgsql:era
DROP FUNCTION IF EXISTS era(INTEGER);

CREATE OR REPLACE FUNCTION era(first_appeared INTEGER)
RETURNS TEXT AS $$
  BEGIN
    IF first_appeared < 1990 THEN
      RETURN 'ancient';
    ELSEIF first_appeared >= 1990 AND first_appeared < 2000 THEN
      RETURN 'middle';
    ELSE
      RETURN 'modern';
    END IF;
  END;
$$ LANGUAGE plpgsql;
```

`era()`では`IF`を使っています。書式は次の通りです。

<pre>IF 条件式 THEN
  処理
ELSEIF 条件式 THEN
  処理
ELSE
  処理
END IF;
</pre>

`END IF;`を忘れないようにしてください。

実行してみましょう。

```sql
pltest=# SELECT id, name, first_appeared, era(first_appeared) FROM programming;
 id |  name  | first_appeared |   era
----+--------+----------------+---------
  1 | Lisp   |           1958 | ancient
  2 | C      |           1972 | ancient
  3 | SQL    |           1974 | ancient
  4 | python |           1991 | middle
  5 | Java   |           1995 | middle
  6 | PHP    |           1995 | middle
  7 | Scala  |           2004 | modern
  8 | Rust   |           2010 | modern
```
OKです。

### 繰り返し
PL/pgSQLではいくつかの繰り返しが使えます。

- FOR
- FOREACH
- WHILE
- LOOP(無限LOOP)

ここではFORとFOREACHを使います。

#### FOR
`FOR`を使うと、数字をインクリメントしていくような繰り返しが書けます。
例として、
同じ年代に登場したプログラミング言語を同時にINSERTする関数add_all_lang()を作ります。

つまり、

```
pltest=# SELECT add_all_lang('{"Swift", "Hack"}', 2014);
pltest=# SELECT * FROM programming;
```
を実行すると、次の表ができるようにします。

 id |  name  | first_appeared
----+--------+----------------
  1 | Lisp   |           1958
  2 | C      |           1972
  3 | SQL    |           1974
  4 | python |           1991
  5 | Java   |           1995
  6 | PHP    |           1995
  7 | Scala  |           2004
  8 | Rust   |           2010
  9 | Swift  |           2014
 10 | Hack   |           2014

「SwiftとHackが2014年に登場した」というデータ追加されます。

add_all_lang()は次のようになります。

```plpgsql:add_all_lang
DROP FUNCTION IF EXISTS add_all_lang(VARCHAR(255)[], INTEGER);

CREATE OR REPLACE FUNCTION add_all_lang(names VARCHAR(255)[], first_appeared INTEGER)
RETURNS VOID AS $$
  BEGIN
    FOR i IN 1 .. array_length(names, 1) LOOP
      INSERT INTO programming(name, first_appeared)
        VALUES(names[i], first_appeared);
    END LOOP;
  END;
$$ LANGUAGE plpgsql;
```

PL/pgSQLでは、**配列のindexは1から始まる**のに注意してください。
FORの書式は次の通りです。

<pre>
FOR 変数名 IN 範囲 LOOP
  処理
END LOOP;
</pre>
以下は補足です。

- 配列を引数でとるには、`型名[]`にします。反対に、配列を引数で渡したいときは`"{item1, item2 ...}"`とします。
- add_all_lang()は副作用のための関数なので、戻り値はありません。その場合は、`RETURNS VOID`です。
- array_length()は配列の要素数を取得する関数です。第二引数は配列の次元です。今回は１次元配列なので、１を指定します。

#### FOREACH
では、add_all_lang()の`FOREACH`版を作ります。
`FOREACH`は配列をイテレートするような処理に向いています。

add_all_lang()は次のようになります。

```plpgsql:add_all_lang
DROP FUNCTION IF EXISTS add_all_lang(VARCHAR(255)[], INTEGER);

CREATE OR REPLACE FUNCTION add_all_lang(names VARCHAR(255)[], first_appeared INTEGER)
RETURNS VOID AS $$
  DECLARE
    name VARCHAR(255);
  BEGIN
    FOREACH name IN ARRAY names LOOP
      INSERT INTO programming(name, first_appeared)
        VALUES(name, first_appeared);
    END LOOP;
  END;
$$ LANGUAGE plpgsql;
```

`FOREACH`は配列のサイズを気にせずに繰り返すにはいいです。
書式は次のようになります。

<pre>FOREACH 変数名 IN ARRAY 配列の変数 LOOP
  処理
END LOOP;
</pre>

ただし、`FOREACH`に指定する`変数名`は**あらかじめ宣言されている必要があります**。
ですので、add_all_lang()のFOREACH版には`DECLARE`が追加されています。

```
DECLARE
    name VARCHAR(255);
```

この点は宣言のいらない`FOR`と異なります。

#### その他の繰り返し
ほかの繰り返しも基本は同じです。
使いたくなったら公式リファレンスを見るといいです。m(_ _)m
https://www.postgresql.jp/document/9.2/html/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

## コンソールに出力する
デバッグするときはコンソールに出力すると便利です。
PL/pgSQLでもそれができます。

では、受け取ったメッセージを出力する関数print()を作成してみます。

```plpgsql:print
DROP FUNCTION IF EXISTS print(TEXT);

CREATE OR REPLACE FUNCTION print(message TEXT)
RETURNS VOID AS $$
  BEGIN
    RAISE INFO '%', message;
  END;
$$ LANGUAGE plpgsql;
```

次のように実行します。

```
pltest=# select print('Hello, World!');
INFO:  Hello, World!
```

PL/pgSQLでコンソールに出力するには`RAISE`を使います。
書式は次のとおりです。

```
RAISE レベル フォーマット, フォーマットに埋め込む変数;
```
次のレベルが指定できます。

- DEBUG
- LOG
- INFO
- NOTICE
- WARNING
- ERROR
- EXCEPTION(デフォルト)

print()ではINFOを選択しました。

フォーマット内で`%`を使うと、その部分が変数の値に置き換わります。

RAISEのドキュメントはこちら
https://www.postgresql.jp/document/9.2/html/plpgsql-errors-and-messages.html

# まとめ
RDBMSの関数は、パフォーマンスの面で有利になる場合があるかもしれません。
正直、積極的に活用したいとは思いませんが、知っておくと後々使いたい日が来るかもしれません。
基本が分かれば、ドキュメントをみて関数を作れそうですね。

ドキュメント
https://www.postgresql.jp/document/9.2/html/index.html