Re: plperl trigger problem

["BigSmoke" <bigsmoke(at)gmail(dot)com>]

On Dec 20, 9:02 am, "Christian Maier" <tomtai(dot)(dot)(dot)(at)freesurf(dot)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.