Thread: plpgsql function to validate e-mail
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é.
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é.
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
>
> 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 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
>
> 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
>