evilrouters.net

at work, i use both the mysql and postgresql relational database systems. my personal preference is for postgresql, though i’m often forced to use mysql as more software seems to support it. for homebrew applications, however, i almost always use postgresql.

in addition, i am a huge fan of perl. i use it almost exclusively for my various scripts which interact with databases (dbi ftw!). lots of these are “batch jobs” where a data file is obtained from one (often legacy) system and is then imported into our own database where it is acted upon.

sometimes it is necessary to notify certain folks when new data has been added to a database. often this is addressed in the perl scripts themselves by using net::smtp to craft an e-mail message immediately after a sql insert operation takes place.

one thing that postgresql supports that mysql doesn’t is the ability to write trigger functions in a variety of languages, including perl. this means that i can take code that normally executes in my scripts and is instead executed by the database: more specifically, i can send e-mails from within postgresql itself and not have to worry about it in my code.

for the full details, see “chapter 37: pl/perl – perl procedural language” in the postgresql manual. for the short version, well, keep reading.

note: there is trusted and untrusted pl/perl. our example uses untrusted pl/perl. you’ll probably want to read about the differences (and potential security implications).

the first thing we need to do is install pl/perl into our database (it is not enabled by default). you’ll do this from the command-line using the “createlang” executable that comes with postgresql. to install pl/perl into the “mydb” database, run:

$ createlang plperlu mydb

next, we’ll create a trigger function named “mydb_mytable_insert_send_mail”. the name is totally up to you, but since i only look at the functions perhaps once a year, i like them to be very descriptive so that i can tell at a glance exactly what they do. the trigger function must be created while you are connected to the “mydb” database. i use the psql command-line client:

mydb=# CREATE OR REPLACE FUNCTION mydb_mytable_insert_send_mail_function()
mydb-# RETURNS "trigger" AS
mydb-# $BODY$
mydb$# use Mail::Sendmail;
mydb$#
mydb$# $message = "A new entry has been added to the 'mytable' table.\n\n";
mydb$# $message .= "The new name is:  $_TD->{new}{name}\n\n";
mydb$#
mydb$# %mail = ( From => $_[0], To => $_[1], Subject => $_[2], Message => $message);
mydb$#
mydb$# sendmail(%mail) or die $Mail::Sendmail::error;
mydb$# return undef;
mydb$# $BODY$
mydb-# LANGUAGE 'plperlu' VOLATILE;

note: the hash reference $_TD contains information about the current trigger event. “name” above refers to a column in the “mydb” database.

now that we have the trigger function created, we have to tell postgresql when to run it. in this case, we want it to fire whenever a new row is added to the “mytable” table in the “mydb” database.

mydb=# CREATE TRIGGER mydb_mytable_insert_send_mail_trigger
mydb=# AFTER INSERT ON mytable
mydb=# FOR EACH ROW
mydb=# EXECUTE PROCEDURE mydb_mytable_insert_send_mail_function('from@domain.com', 'to@domain.com', 'subject here');

and that’s it! modify the last line as appropriate to contain the appropriate e-mail addresses and the subject line. you can test for proper operation by simply inserting a new row into the “mytable” table.