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 !~ ''(dot)(at)(dot)'' 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 ~* '^[^(at)]+@[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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant