469,645 Members | 1,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

PL/Perl Trigger Problem

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
1 3934
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.

Similar topics

1 post views Thread by joy | last post: by
3 posts views Thread by Daniel Moree | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
2 posts views Thread by dean.cochrane | last post: by
7 posts views Thread by Wojto | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.