Edited at

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

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なので、LANGUAGEplpgsqlです。

コードの通り、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()は配列の要素数を取得する関数です。第二引数は配列の次元です。今回は1次元配列なので、1を指定します。


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


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