PostgreSQLでemail、creditCardチェックメモ

メールアドレス

yum install postgresql84-plperl

yum install perl-Email-Address

-- Language: plperlu
-- DROP LANGUAGE plperlu;
CREATE PROCEDURAL LANGUAGE 'plperlu';

-- Function: is_email(email text)
-- DROP FUNCTION is_email(email text);
CREATE OR REPLACE FUNCTION is_email(email text)
  RETURNS bool AS
$$
use Email::Address;

return 1 unless defined($_[0]);
my @addresses = Email::Address->parse($_[0]);
return scalar(@addresses) > 0 ? 1 : 0;
$$
  LANGUAGE 'plperlu' VOLATILE;

-- Domain: email
-- DROP DOMAIN email;
CREATE DOMAIN email AS text
  CHECK (is_email(VALUE));

クレジットカード

yum install postgresql84-plperl

cpan install Business::CreditCard

-- Function: is_credit_card(credit_card text)

-- DROP FUNCTION is_credit_card(credit_card text);

CREATE OR REPLACE FUNCTION is_credit_card(credit_card text)

  RETURNS bool AS

$$

use Business::CreditCard;

return 1 unless defined($_[0]);

return validate($_[0]) > 0 ? 1 : 0;

$$

  LANGUAGE 'plperlu' VOLATILE;

-- Domain: credit_card

-- DROP DOMAIN credit_card;

CREATE DOMAIN credit_card AS text

   CHECK (is_credit_card(VALUE));