Re: [GENERAL] plpgsql function to validate e-mail
2009/8/17 David Fetter : > On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote: >> 2009/8/17 David Fetter : >> > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote: >> >> Hello >> >> >> >> 2009/8/16 Andre Lopes : >> >> > 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 http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote: > 2009/8/17 David Fetter : > > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote: > >> Hello > >> > >> 2009/8/16 Andre Lopes : > >> > 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
2009/8/17 David Fetter : > On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote: >> Hello >> >> 2009/8/16 Andre Lopes : >> > 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 http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote: > Hello > > 2009/8/16 Andre Lopes : > > 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 http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
Hello 2009/8/16 Andre Lopes : > 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 'steh...@kix.fsv.cvut.cz'::email; email - steh...@kix.fsv.cvut.cz (1 row) regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
Tom Lane wrote: Andre Lopes 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql function to validate e-mail
Andre Lopes 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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é.