Re: plpgsql function to validate e-mail

2009/8/17 David Fetter <david(at)fetter(dot)org>:
> On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote:
>> 2009/8/17 David Fetter <david(at)fetter(dot)org>:
>> > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
>> >> 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;
>> >
>> > 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(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>