Thread: plperl trigger problem

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

"Christian Maier" <tomtailor@freesurf.fr> writes:
> 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.

The mistake is that you're imagining that the trigger runs before the
data is converted to internal format.  You can't really fix it this way.

What I'd suggest is loading the cvs file into a temp table in which the
bogus column is declared as text rather than float8.  Then you can do
the data correction as a simple UPDATE on that table, and finally load
the data into the real table with INSERT/SELECT.

Alternatively, fix the file with a perl or sed script before you load it.

            regards, tom lane