Re: plpgsql function to validate e-mail

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.

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