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 ($_[0]));
my @address = Email::Address->parse($_[0]);
$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
$BODY$
use Email::Address;
my $input = shift;
return('TRUE') if ($input eq '');
my @address = Email::Address->parse($input);
return (@address == 1 ? 'TRUE' : 'FALSE');
$BODY$
LANGUAGE plperlu
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.
return $answer if ((not defined $_[0]) or ($_[0] == ''));Orreturn undef if ((not defined $_[0]) or ($_[0] == ''));. Returning NULL will still work for theCHECKconstraint. – ypercubeᵀᴹ May 24 '19 at 07:36`CREATE OR REPLACE FUNCTION email_valid(email text)
RETURNS boolean AS
$BODY$
procedure to check valid email address
TRUE if NULL or only 1 email address
otherwise FALSE
use Email::Address;
my $input = shift;
return('TRUE') if ($input eq '');
my @address = Email::Address->parse($input);
return (@address == 1 ? 'TRUE' : 'FALSE');
$BODY$
LANGUAGE plperlu `
– bgroper May 25 '19 at 02:39Structure of employees table is plain vanilla. Usual details, Name, Address, etc, and column called "email".
– bgroper May 25 '19 at 02:45