In my latest adventures in code I've been using PLPerlNG, which is a Perl procedural language for the PostgreSQL database. In the past when I've needed to munge data before creating a new object with Class::DBI, I override the create method or create a before_create trigger.
I wanted to try something different this time, so after some frustration with trying to get this particular trigger working in the plpgsql language, I decided to write it in PLPerl. For this implementation, I have an image table which I map locations of images to, and if the application does not provide a name for the image I use the filename sans extension for the name. Here's the code:
CREATE OR REPLACE FUNCTION image_insert() RETURNS trigger as $$
if ( ! defined $_TD->{new}{name} || $_TD->{new}{name} eq '') {
# Assign the name of the file to name of the image if no name specified
($_TD->{new}{name}) = $_TD->{new}{uri} =~ m{([^/]*)\.\w+$};
return "MODIFY"; # Modify tuple and proceed INSERT/UPDATE command
} else {
return; # Proceed INSERT/UPDATE command
}
$$
language plperl;
CREATE TRIGGER image_insert
BEFORE INSERT ON image
FOR EACH ROW
EXECUTE PROCEDURE image_insert();