Re: plpgsql function to validate e-mail

Hello

2009/8/16 Andre Lopes <lopes80andre@gmail.com>:
> Hi,
>
> I need a plpgsql function to validade e-mail addresses. I have google but I
> can't find any.
>
> My question: Anyone have a function to validate e-mails?
>
> Best Regards,
> André.
>

You don't need plpgsql. Important is only an using of regular expression.

very strong validation should be done via plperlu

CREATE OR REPLACE FUNCTION check_email(varchar)
RETURNS boolean AS $$
use strict;
use Email::Valid;
my $address = $_[0];
my $checks = {
   -address => $address,
   -mxcheck => 1,
   -tldcheck => 1,
   -rfc822 => 1,
};
if (defined Email::Valid->address( %$checks )) {
    return 'true'
}
elog(WARNING, "address failed $Email::Valid::Details check.");
return 'false';
$$ LANGUAGE plperlu IMMUTABLE STRICT;

postgres=# CREATE DOMAIN email AS varchar CHECK(check_email(value));
CREATE DOMAIN
postgres=# SELECT 'pavel@'::email;
WARNING:  address failed rfc822 check.
postgres=# select 'stehule@kix.fsv.cvut.cz'::email;
          email
-------------------------
 stehule@kix.fsv.cvut.cz
(1 row)

regards
Pavel Stehule