From: | "Christian Maier" <tomtailor(at)freesurf(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | plperl trigger problem |
Date: | 2006-12-20 08:02:31 |
Message-ID: | 1166601751.545227.119850@80g2000cwy.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannes Dorbath | 2006-12-20 08:20:09 | Re: (bash the search engine) Don't split on underscore |
Previous Message | Richard Huxton | 2006-12-20 07:11:58 | Re: Unable to start server - winxp |