Postgres Function to Validate Email Address

Question

A check constraint which would call a function to validate email addresses is not working fine for me.

CREATE OR REPLACE FUNCTION f_IsValidEmail(text) returns BOOLEAN AS 
'select $1 ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
' LANGUAGE sql;



SELECT f_IsValidEmail('[email protected]');

The function is returning false, which should be true. I have tried a couple of other regexs but in vain. Can anyone point out what's wrong with this function?

Screenshot

Answer 1

I recommend a solution using PL/Perl and the Email::Address module. Something like the following:

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

See also http://wiki.postgresql.org/wiki/Email_address_parsing.

Answer 2

Don't attempt to create a regex to validate e-mails!

It is notoriously difficult to accomplish. Here's a better solution:

Assuming that you have Perl installed on your database host, install the Email::Valid module on the same host using CPAN:

you@localhost$ cpan Email::Valid

Then make sure that you have PL/Perl installed. Connect to your database in psql and add plperlu as a language:

CREATE EXTENSION plperlu;

(Keep in mind that this is an untrusted language, so you'll be giving your db direct file access, which could pose a security risk if someone were to insert malicious code into your Perl modules or into the db functions. However, you need to do it for the next step.)

Add the following function to your database:

CREATE FUNCTION validate_email() RETURNS trigger AS $$
  use Email::Valid;
  return if Email::Valid->address($_TD->{new}{email});
  elog(ERROR, "invalid email address $_TD->{new}{email} inserted into $_TD->{table_name}(email)");
  return "SKIP";
$$ LANGUAGE plperlu;

Add a trigger constraint to your table on your column (assuming that your table is named "users" and your column is named "email"):

CREATE TRIGGER users_valid_email_trig
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE validate_email();

And you're done!

This solution uses the Email::Valid Perl module to handle validation, which in turn relies on a regex to ensure RFC 822 compliance. However, it is a monster of a regex, so don't try to come up with your own.

If you feel uncomfortable with enabling plperlu instead of plain plperl, you could probably port the relevant functions into your database.