[ale] perl trigger in postgresql

[Chris Woodfield]

All right, go me!

The mistake was in the perl logic, not in the trigger implementation -  
the two ne tests should have been ANDed, not ORed - if you OR two ne  
tests on the same variable, one is guaranteed to be not true, hence  
the test will always be true.

This corrected version works as expected:

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

</duncecap>

-Chris

On May 10, 2008, at 1:06 PM, Chris Woodfield wrote:

> 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
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
>