[ale] perl trigger in postgresql

Chris Woodfield rekoil at semihuman.com
Sat May 10 13:06:53 EDT 2008


I hope this isn't an obscure subject...

I have a table in a postgres DB that absolutely, positively, needs to  
be a legal URL string. While my perl frontends handle this via the URI- 
 >canonical function, I'd like to build an additional check in the DB  
via a trigger.

This is the first time I've done this, so it's likely I've made an  
obvious error somewhere...

Here's what's in the DB now (from pg_dump):

CREATE FUNCTION canonical_url() RETURNS "trigger"
     AS $_X$
use strict;
use URI;
if (($_TD->{event} ne 'INSERT') or ($_TD->{event} ne 'UPDATE')) {
     return;
} else  {
     my $url = new URI($_TD->{new}{url});
     $_TD->{new}->{url} = $url->canonical();
     return 'MODIFY';
}
$_X$
     LANGUAGE plperlu;

CREATE TRIGGER make_canonical_url
     BEFORE INSERT OR UPDATE ON url_table
     FOR EACH ROW
     EXECUTE PROCEDURE canonical_url();

However, when I do an insert on the table with illegal URL characters  
(such as spaces or double quotes), the url appears in my table as is,  
as if the trigger didn't get called, or the trigger function didn't  
modify the column.

Any ideas? Thanks in advance...

-Chris



More information about the Ale mailing list