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