1

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.

bgroper
  • 21
  • 2
  • 2
    Assume that we know zero Perl. What does the function return when the parameter is an empty string? – ypercubeᵀᴹ May 24 '19 at 07: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 '19 at 07:36
  • 1
    What is your table employee structure ? Please post ddl – Arkhena May 24 '19 at 09: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 '19 at 02:39
  • @Arkhena

    Structure of employees table is plain vanilla. Usual details, Name, Address, etc, and column called "email".

    – bgroper May 25 '19 at 02:45
  • Oh, you're right, my bad.. Should I delete my answer as it's not accurate ? – Arkhena May 27 '19 at 09:35
  • Thanks to all who helped me find a good solution to my question. – bgroper May 28 '19 at 00:29

0 Answers0