雑記帳

■ [PostgreSQL] PostgreSQLでE-mailアドレスのドメインを追加する

PostgreSQLは、ドメイン（いわゆるデータ型）を定義することができる。よく使うデータの代表として、E-mailアドレスがある。これをドメインとして、定義したいと思う。

E-mailをチェックする方法として、いくつか方法があると思う。今回は下記のページを参考にしてやってみる。

http://stackoverflow.com/questions/5689718/how-can-i-create-a-constraint-to-check-if-an-email-is-valid-in-postgres

ここでは、PostgreSQLの拡張であるplperluからPerl ModuleであるEmail::Addressを使ってチェックしている。

今回利用する環境は、CentOS 7に http://yum.postgresql.org/ のyumリポジトリからPostgreSQL 9.4をインストールしている。

Perlモジュールをインストールする

PerlのEmail::Addressを使うため、先にインストールしておく。

$ sudo yum install -y perl-Email-Address

plperluをインストールする

PostgreSQLでPerlを使うための拡張である、plperluをインストールする。

$ sudo yum install postgresql94-plperl

インストールできたら、PostgreSQLを再起動する。

$ sudo systemctl restert postgresql-9.4

インストールできたのかを確認する。

$ psql -Upostgres
postgres=> select * from pg_available_extensions;
  name   | default_version | installed_version |                comment
---------+-----------------+-------------------+----------------------------------------
 plpgsql | 1.0             | 1.0               | PL/pgSQL procedural language
 plperl  | 1.0             | 1.0               | PL/Perl procedural language
 plperlu | 1.0             |                   | PL/PerlU untrusted procedural language
(3 行)

plperluが含まれているので、利用できるようだ。

データベースで拡張を使えるようにする

今回はexampleデータベースで使う。

example=# create extension plperlu;
CREATE EXTENSION

拡張の一覧を確認する。

example=# \dx
                        インストール済みの拡張の一覧
  名前   | バージョン |  スキーマ  |                  説明
---------+------------+------------+----------------------------------------
 plperl  | 1.0        | pg_catalog | PL/Perl procedural language
 plperlu | 1.0        | pg_catalog | PL/PerlU untrusted procedural language
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(3 行)

plperluの他にも、plperl, plpgsqlが利用できる状態であるようだ。

E-mailをチェックする関数を作成する

下記のSQLを実行する。

CREATE OR REPLACE FUNCTION check_email(email text) RETURNS bool LANGUAGE plperlu AS $$
  use Email::Address;
  my @addresses = Email::Address->parse($_[0]);
  return scalar(@addresses) > 0 ? 1 : 0;
$$;

問題なければ、関数として登録される。

example=# \df
                                 関数一覧
 スキーマ |    名前     | 結果のデータ型 | 引数のデータ型 |       型
----------+-------------+----------------+----------------+----------------
 public   | check_email | boolean        | email text     | normal（通常）
(1 行)

ドメインを作成する

ここまでで、やっとドメインを作る準備ができた。CREATE DOMAINを実行して、ドメインを作成する。

example=# create domain email_address AS text check (check_email(VALUE));
CREATE DOMAIN

問題ないか確認する。

example=# \dD
                                       ドメイン一覧
 スキーマ |        名前        |   型   |  修飾語  |                チェック
----------+--------------------+--------+----------+----------------------------------------
 public   | email_address      | text   |          | CHECK (check_email(VALUE))
(1 行)

試す

試しにテーブルを作成して、動作を確認する。

example=# create table emails (email_address email_address);
CREATE TABLE

紛らわしいSQLになってしまった。まぁいい。データを投入してみる。

example=# insert into emails (email_address) values ('test@example.com');
INSERT 0 1

入った。では、E-mailアドレスではないものを入れてみる。

adminpack_development=# insert into emails (email_address) values ('test.@example.com');
ERROR:  ドメインemail_addressの値が検査制約"email_address_check"に違反しています

ちゃんとエラーになった！ 素晴らしい。

これで、E-mailが必要な場面で、アプリによるE-mailを検証する実装が妥当でなくても、データベースを守ることができる。