PostgreSQLは、ドメイン（いわゆるデータ型）を定義することができる。よく使うデータの代表として、E-mailアドレスがある。これをドメインとして、定義したいと思う。
E-mailをチェックする方法として、いくつか方法があると思う。今回は下記のページを参考にしてやってみる。
ここでは、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を検証する実装が妥当でなくても、データベースを守ることができる。
