plpgsql function to validate e-mail

On 16/08/2009 21:10, Andre Lopes wrote:

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?

There are lots of regular expressions which Google will find for you,
which you can then use with one of the built-in functions and operators
that can use them.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Andre Lopes <lopes80andre@gmail.com> writes:

My question: Anyone have a function to validate e-mails?

Check the PG archives --- this has been discussed before. IIRC you
can't *really* validate them, short of actually sending mail.
But there are partial solutions in the archives.

regards, tom lane

Tom Lane wrote:

Andre Lopes <lopes80andre@gmail.com> writes:

My question: Anyone have a function to validate e-mails?

Check the PG archives --- this has been discussed before. IIRC you
can't *really* validate them, short of actually sending mail.

And getting a reply.

But there are partial solutions in the archives.

Even a valid email can be invalid. I periodically receive emails meant for a
parent of a ten-year child who has signed up for a kids' site, but who
misspelled their parent's email address and put mine in by mistake. I also
get emails meant for a contractor in New England whose email address is
similar to mine, but has a zero where mine has an "o" (letter "oh"). In both
cases the email address is valid in and of itself, but is not valid for the
purpose intended.

--
Lew

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

On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:

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;

If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2009/8/17 David Fetter <david@fetter.org>:

On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:

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;

If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.

This is your code, If I remember well :). I am not sure, if immutable
is incorrect flag. Maybe STABLE is better. This check should be used
very carefully. But it's really strong, much more exact than only
regular expression.

Pavel

On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote:

2009/8/17 David Fetter <david@fetter.org>:

On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:

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;

If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.

This is your code, If I remember well :).

Yes, it's mine, but you'll recall I'd routinely ask the audience,
"what's wrong with this code?" and one of the things I mentioned was
its essential mutability. ;)

I am not sure, if immutable is incorrect flag. Maybe STABLE is
better. This check should be used very carefully. But it's really
strong, much more exact than only regular expression.

It depends what you mean. If it stands a 99.9% chance of being
right...but only when the network is up, then it's not really beating
a regex because it's introducing an essential indeterminacy. There
are other indeterminacies it introduces like the fact that an email
can become invalid and valid again over time.

When creating constraints, something that looks outside the database
is initially cute, but turns out to be a really, really bad idea.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, 2009-08-16 at 21:10 +0100, Andre Lopes wrote:

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?

I recommend something based on the following recipe in PL/Perl.

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

Rewriting that complete logic in PL/pgSQL will probably be very tricky.

2009/8/17 David Fetter <david@fetter.org>:

On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote:

2009/8/17 David Fetter <david@fetter.org>:

On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:

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;

If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.

This is your code, If I remember well :).

Yes, it's mine, but you'll recall I'd routinely ask the audience,
"what's wrong with this code?" and one of the things I mentioned was
its essential mutability. ;)

I am not sure, if immutable is incorrect flag. Maybe STABLE is
better. This check should be used very carefully. But it's really
strong, much more exact than only regular expression.

It depends what you mean.  If it stands a 99.9% chance of being
right...but only when the network is up, then it's not really beating
a regex because it's introducing an essential indeterminacy.  There
are other indeterminacies it introduces like the fact that an email
can become invalid and valid again over time.

yes - but you can expect, so validation of email is little bit longer
then transaction time. You can save some time, because you save some
expensive network IO.

Pavel