Re-inventing Greg's method to prevent re-inventing

A few weeks ago, Greg posted a very nice example of using pl/perl to ensure email validity inside your database. Certainly the principals of the post all make sense; make use of a database function to do your validity checking and save yourself from having to re-implement that logic in various applications that interact with your database. Of course, with PostgreSQL’s wide array of procedural language support, there’s more than one way to do it.

So the way I’ve decided to re-implement this is by using pl/php. As its name implies, pl/php is a PHP based procedural language that can be use for most tasks where other procedural languages can be used inside of a PostgreSQL database. It is developed primarily by Command Prompt, Inc. and is available as open source software. Now, the thing that was so nice about Greg’s example was that he didn’t just write a little regular expression to do validation, rather he used a well developed CPAN module to do his dirty work. When I started to re-implement this in pl/php, I found no end of one off hacks for doing this in PHP, there are actually several posted right in the manual, but I wanted something that would be more robust. After a bit of digging, I came across Pear:Validate. As it’s name implies, Pear:Validate is a package design to give robust, reusable validation routines from inside PHP. In my case it was just what I needed.


CREATE OR REPLACE FUNCTION valid_email(text)
RETURNS boolean
IMMUTABLE
LANGUAGE plphpu
AS $$
    require_once 'Validate.php';

    $validate = new Validate();

    return $validate->email("$args[0]",array("check_domain"=>false,"use_rfc822"=>true)) ? 1 : 0;
$$;

Thats it! In keeping with the spirit of things, I’ve installed this into our pagila sample database, and then tested out a few email addresses:


pagila=# SELECT valid_email('[email protected]');
 valid_email
-----------
 t
(1 row)
pagila=# SELECT valid_email ('Robert Treat <xzilla@ users . sf . net>');
 valid_email
-----------
 t
(1 row)
pagila=# SELECT valid_email('www.brighterlamp.org');
 valid_email
-----------
 f
(1 row)

Yep, PEAR:Validate was able to handle the whitespace and leading text of the second example just fine. And just like any other function, we can also go ahead and use this function as part of a domain and incorporate that change into our database tables as needed.


pagila=# CREATE DOMAIN validemail AS text
pagila-# NOT NULL
pagila-# CHECK ( valid_email(VALUE) );
CREATE DOMAIN
pagila=#
pagila=# ALTER TABLE customer ALTER email TYPE validemail ;
ALTER TABLE

As you can see, it is very easy to incorporate this change into an existing system, and sharp readers will realize that we just verified the validity of all of the emails in our database as well!

For those that want to see what an error looks like, we can show you that too:


pagila=# INSERT INTO customer (store_id, first_name, last_name, email, address_id, active)
pagila-# VALUES (2,'Gregis','Mijiro','gtsm123@aol',40,1);
ERROR:  value for domain validemail violates check constraint "validemail_check"

And wa-la! Friendly error message, centralized logic, and easy to update with a simple change to the valid_email function. While pl/php may not be as polished as something like pl/perl, you can still get a lot of gooey goodness out of it for those who are inclined to lean toward PHP.

Note: I’ll be at php/db|works in Toronto next week doing a talk on pl/php, showing this and a couple of other neat tricks. If you’re planning to attend, you’ve just gotten a sneak peek. If you’re not planning to attend, it’s not too late to change your mind.