I been using postgres for a while. Mostly simple stuff.
Now trying to implement a small perl function to check for a single valid email address.
My employees table should have either a single valid email address, or none (ie empty, zilch). I assume none means undef(email)
My sample code below.
I'm trying to return FALSE only if the given variable is NOT an email address.
The function appears to correctly check a given email address, BUT wrongly returns FALSE if email is blank or empty.
Grateful for any help to show me how to do this the-right-way.
CREATE OR REPLACE FUNCTION email_valid(email text) RETURNS boolean AS $BODY$ use Email::Address; my $answer = 1; return $answer if (undef ($_)); my @address = Email::Address->parse($_); $answer = scalar(@address); return $answer; $BODY$ LANGUAGE plperlu; ALTER TABLE employees ADD CONSTRAINT check_email CHECK (email_valid(email) = TRUE) ;
Edit. Here's the revised procedure commented below, in easier to read format. :-)
CREATE OR REPLACE FUNCTION public.email_valid(email text) RETURNS boolean AS
my $input = shift;
return('TRUE') if ($input eq '');
my @address = Email::Address->parse($input);
return (@address == 1 ? 'TRUE' : 'FALSE');
This is a different question to the similar previous question at What is the best way to store an email address in PostgreSQL?
I need to also allow null or blank email address, whereas other question and answer will require valid e-address.