Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

SRsawaguchi
Webサービス作ってます。
http://lambda.tokyo
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away