plperl trigger problem

From: "Christian Maier" <tomtailor(at)freesurf(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: plperl trigger problem
Date: 2006-12-20 08:02:31
Message-ID: 1166601751.545227.119850@80g2000cwy.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry I have not found a plperl group so I hope the useres of this
group can help me.

I have some csv data and some columns are in a bad format. this means
that some float values are formatet as " ." if the are 0. So I have
made an before insert trigger that looks for these values and replaces
them but it dont work and I cannot found the mistake.

Please Help.

Here is the Trigger Function

CREATE OR REPLACE FUNCTION valid_number()
RETURNS "trigger" AS
$BODY$
#hier die neue trigger-function
my $val = $_TD->{new};
if (ref $val eq 'HASH')
{
my $str = "";
foreach my $rowkey (keys %$val)
{
$str .= ", " if $str;
my $rowval = $val->{$rowkey};

#if ($temp = (val->{$rowkey} =~ m/\s*\.$/))
if (val->{$rowkey} =~ m/\s*\.$/)
{
$val->{$rowkey} => 0;
}
$str .= "'$rowkey' => '$rowval'->'" . $val->{$rowkey} . "'";
}
elog(NOTICE, "\$_TD->\{$key\} = \{$str\}\n");
}
return "MODIFY";
$BODY$
LANGUAGE 'plperl' VOLATILE;

Than:
copy cot_data from 'D:/Eigene Dateien/PerlProjekte/cot/deacom.txt' with
delimiter as ',' CSV;

ERROR: invalid input syntax for type double precision: " ."
SQL state: 22P02
Context: COPY cot_data, line 26, column change_in_open_interest_all: "
."

Some sample Data can be found at
http://www.cftc.gov/dea/newcot/deacom.txt

THX!
Christian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Dorbath 2006-12-20 08:20:09 Re: (bash the search engine) Don't split on underscore
Previous Message Richard Huxton 2006-12-20 07:11:58 Re: Unable to start server - winxp