Re: plpgsql function to validate e-mail

Hello

2009/8/16 Andre Lopes <lopes80andre(at)gmail(dot)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(at)kix(dot)fsv(dot)cvut(dot)cz'::email;
email
-------------------------
stehule(at)kix(dot)fsv(dot)cvut(dot)cz
(1 row)

regards
Pavel Stehule