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

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

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

準備

関数を実行して試すとき、簡単なテーブルがあると便利です。
そこで、私が使ったものをどうぞ。
次のSQLを保存して実行します。
PostgreSQLならば、データベースに接続して、\i test.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の値を引いたものを返す関数があればいいですね。
その関数はこうなります。

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に追加してみましょう。

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を引数にとってみます。

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;の間に処理を書きます。

BEGIN
  処理
END;

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

関数の実行

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

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()を書き換えます。

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

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

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を使っています。書式は次の通りです。

IF 条件式 THEN
  処理
ELSEIF 条件式 THEN
  処理
ELSE
  処理
END IF;

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

実行してみましょう。

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()は次のようになります。

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の書式は次の通りです。


FOR 変数名 IN 範囲 LOOP
  処理
END LOOP;

以下は補足です。

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

FOREACH

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

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

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は配列のサイズを気にせずに繰り返すにはいいです。
書式は次のようになります。

FOREACH 変数名 IN ARRAY 配列の変数 LOOP
  処理
END LOOP;

ただし、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()を作成してみます。

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

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

準備

SQLによる関数

関数の作成

関数の実行

PL/pgSQLによる関数

関数の作成

関数の実行

変数

制御文

条件分岐

繰り返し

FOR

FOREACH

その他の繰り返し

コンソールに出力する

まとめ