1

This question already has an answer here:

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.

marked as duplicate by Evan Carroll postgresql May 27 at 20:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    Assume that we know zero Perl. What does the function return when the parameter is an empty string? – ypercubeᵀᴹ May 24 at 7:31
  • Try return $answer if ((not defined $_[0]) or ($_[0] == '')); Or return undef if ((not defined $_[0]) or ($_[0] == ''));. Returning NULL will still work for the CHECK constraint. – ypercubeᵀᴹ May 24 at 7:36
  • 1
    What is your table employee structure ? Please post ddl – Arkhena May 24 at 9:33
  • Improved code as per below. This seems to be working as intended, further testing to follow, before I can mark this Question as Answered. 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 at 2:39
  • @Arkhena Structure of employees table is plain vanilla. Usual details, Name, Address, etc, and column called "email". – bgroper May 25 at 2:45