Re: newbie: Column CHECK(col contains '@') ?

> > CREATE FUNCTION valid_email(TEXT) RETURNS BOOLEAN AS '
> >   DECLARE
> >     email ALIAS FOR $1;
> >     user TEXT;
> >     domain TEXT;
> >   BEGIN
> >     IF email !~ ''.@.'' THEN
> >       RETURN FALSE; -- One @ good
> >     END IF;
> >     IF email ~ ''@.*@'' THEN
> >       RETURN FALSE; -- Two @s bad
> >     END IF;
> >     domain := substring( email from position( ''@'' in email) + 1 );
> >     user   := substring( email from 1 for position( ''@'' in email) - 1
> > );
> >     IF domain ~* ''([a-z0-9-]+\.)+([a-z])?[a-z][a-z]$'' THEN
> >       -- Only really worth validating the domain
> >       RETURN TRUE;
> >     END IF;
> >     RETURN FALSE;
> >   END;
> > ' LANGUAGE 'plpgsql';
> >
> > This checks for internal spaces as well, and means that the rules for

Fine idea, but be careful about the regex for domains: it tries to ensure
that the TLD ending (.com, .us, etc) is 2-3 characters long. ".intl" and
".info" are both legal TLDs that are four characters long. A better replace
for the line is

> >     IF domain ~* ''([a-z0-9-]+\.)+([a-z])*[a-z][a-z]$'' THEN
                                             ^- note was a ? before

For succintness' sake, though, this seems like overkill: 3 regex matches
that could be collapsed into one. I'd do:

  em ~* '^[^@]+@[a-z0-9-]+\.[a-z]*[a-z][a-z]'

(slightly modified from Andrew's earlier suggestion to include the 2-or-more
chars in TLD)

Not sure how this will play with domains with non-US characters.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant