On Dec 20, 9:02 am, "Christian Maier" <tomtai...@freesurf.fr> wrote:
> 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 athttp://www.cftc.gov/dea/newcot/deacom.txt
Instead of using a trigger, it would be easier to write a plain plperl
function which sanitizes the data before inserting/copying it. I don't
think a trigger can help you much if the input value is in a format
that is incompatible with the column type.