PL/Perl Trigger Problem

[Dunc]

I'm new to Postgres, and getting nowhere with a PL/Perl trigger that
I'm trying to write - hopefully, someone can give me some insight into
what I'm doing wrong. My trigger is designed to reformat /
standardize phone numbers and it looks like this:

CREATE or REPLACE FUNCTION fixphone() RETURNS trigger AS $$
$number .= $_TD->{new}{phone};
$number =~ s/(-|\.|\(|\)| )//g;
$number .= substr($number,0,3) . "." .
substr($number,3,3) . "." .
substr($number,6,4);
$_TD->{new}{phone} .= $number;

return "MODIFY";
$$ LANGUAGE plperl;

CREATE TRIGGER "cust_fixphone" BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE PROCEDURE "fixPhone"();

CREATE TRIGGER "vend_fixphone" BEFORE INSERT OR UPDATE ON vendor
FOR EACH ROW EXECUTE PROCEDURE "fixPhone"();

All I see in my web app, when the trigger fires, is:

DBD::Pg::db do failed: server closed the connection unexpectedly

And, when I try to update a row in psql I see:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Any help would truly be appreciated. Thanks in advance

Dunc

[Jeff Boes]

At some point in time, goo...@dunc-it.com (Dunc) wrote:

>I'm new to Postgres, and getting nowhere with a PL/Perl trigger that
>I'm trying to write - hopefully, someone can give me some insight into
>what I'm doing wrong. My trigger is designed to reformat /
>standardize phone numbers and it looks like this:
>
>
>CREATE or REPLACE FUNCTION fixphone() RETURNS trigger AS $$
> $number .= $_TD->{new}{phone};
> $number =~ s/(-|\.|\(|\)| )//g;
> $number .= substr($number,0,3) . "." .
> substr($number,3,3) . "." .
> substr($number,6,4);
> $_TD->{new}{phone} .= $number;
>
> return "MODIFY";
> $$ LANGUAGE plperl;
>

On 12 Aug 2004 06:44:59 -0700, in comp.databases.postgresql.questions,
goo...@dunc-it.com (Dunc) wrote:

>I'm new to Postgres, and getting nowhere with a PL/Perl trigger that
>I'm trying to write - hopefully, someone can give me some insight into
>what I'm doing wrong. My trigger is designed to reformat /
>standardize phone numbers and it looks like this:
>
>
>CREATE or REPLACE FUNCTION fixphone() RETURNS trigger AS $$
> $number .= $_TD->{new}{phone};
> $number =~ s/(-|\.|\(|\)| )//g;
> $number .= substr($number,0,3) . "." .
> substr($number,3,3) . "." .
> substr($number,6,4);
> $_TD->{new}{phone} .= $number;
>
> return "MODIFY";
> $$ LANGUAGE plperl;
>

You're returning the wrong value. From section 35.1 of the 7.4.3 documentation:

Trigger functions return a table row (a value of type HeapTuple) to the calling
executor. A trigger fired before an operation has the following choices:

* It can return a NULL pointer to skip the operation for the current row
(and so the row will not be inserted/updated/deleted).
* For INSERT and UPDATE triggers only, the returned row becomes the row that
will be inserted or will replace the row being updated. This allows the trigger
function to modify the row being inserted or updated.

A before trigger that does not intend to cause either of these behaviors must be
careful to return as its result the same row that was passed in (that is, the
NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).

So you must return either "undef" to indicate that the row operation is to
discard the insert/update, or (in your case) one of $_TD->{new} or $_TD->{old}.

--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise