By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,781 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,781 IT Pros & Developers. It's quick & easy.

PL/Perl Trigger Problem

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
At some point in time, go****@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,
go****@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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.