PostgreSQLで使える、関数の作り方を簡単にまとめました。
詳しいリファレンスはWebを探せばいくらでもありますが。。。
ちょっと書きたいだけなのに、なかなか簡単なサンプルが見つからない。。。
ということで、学習しながら試したことをまとめてみます。
手軽なものであれば、サンプルを書き換えれば大丈夫です。
でも、せっかくなのでチュートリアル風にしてみました。
動作確認はpsql (PostgreSQL) 9.6.3(> psql --version)で行っています。
言語はSQLとPL/pgSQLを使います。
準備
関数を実行して試すとき、簡単なテーブルがあると便利です。
そこで、私が使ったものをどうぞ。
次のSQLを保存して実行します。
PostgreSQLならば、データベースに接続して、\i 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の値を引いたものを返す関数があればいいですね。
その関数はこうなります。
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を引数にとってみます。
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()を書き換えます。
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
プログラムは次の通りです。
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()は次のようになります。
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()は次のようになります。
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