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

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.

"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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq