Situation:
I'm writing a function that fetches data in an Oracle database and stores it in postgresql database. The function works, but I can't seem to get the error handling right. I get something but it's not what I expect. This is what I get:
executing 14 generated 4 errors
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address Belgium
And this is what I expect to get:
executing 14 generated 4 errors
ERROR: lil foutje Address Belgium
ERROR: lil foutje Address France
ERROR: bol nog een foutje Italie
ERROR: bol nog een foutje Beglie
This is the data in Oracle
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'ash', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Address Belgium', 'lil', 501);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('fr', 'Address France', 'lil', 496);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('it', 'Italie', 'bol', 3138);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '012', 687181679);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'ash', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('be', 'Beglie', 'bol', 3187);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('sp', 'Address Spain', 'bar', 1302174);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International countries', 'as0', 29);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'This country', 'as0', 30);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', '011', 216774985);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', '011', 216774984);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('national', 'National', 'hlm', 451094066);
Insert into addressformatheading (ADDRFORMAT, NAME, DATAAREAID, RECID) Values ('internatio', 'International', 'hlm', 451094067);
This is the target table definition in PostgreSQL
CREATE TABLE "public"."afh_test" (
"addrformat" VARCHAR(10) NOT NULL,
"name" VARCHAR(30) NOT NULL,
"dataareaid" VARCHAR(3) NOT NULL,
"recid" NUMERIC(10,0) NOT NULL
) WITHOUT OIDS;
CREATE UNIQUE INDEX "afh_test_idx" ON "public"."afh_test"
USING btree ("addrformat", "dataareaid");
CREATE TRIGGER "afh_test_tr" BEFORE INSERT
ON "public"."afh_test" FOR EACH ROW
EXECUTE PROCEDURE "public"."temp_func1"();
CREATE OR REPLACE FUNCTION "public"."temp_func1" () RETURNS trigger AS
$body$
BEGIN
IF NEW.dataareaid = 'lil' THEN
RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
elsIF NEW.dataareaid = 'bol' THEN
RAISE EXCEPTION '% nog een foutje %', NEW.dataareaid, NEW.name;
END IF;
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
This is the function that retrieves the Oracle data and inserts it in the target table
CREATE OR REPLACE FUNCTION "public"."dbi_insert3" () RETURNS integer AS
$body$
use DBI;
$query = 'SELECT * FROM AddressFormatHeading';
$target = 'INSERT INTO afh_test (addrformat, name, dataareaid, recid) VALUES (?,?,?,?)';
my $dbh_ora = DBI->connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '8QD6ibmD')
or die "Couldn't connect to database: " . DBI->errstr;
my $dbh_pg = DBI->connect('dbi:Pg:dbname=defrevdev;host=10.100.1.21;port=2345', 'defrevsys', 'Y2I6vbEW')
or die "Couldn't connect to database: " . DBI->errstr;
my $sel = $dbh_ora->prepare($query)
or elog(ERROR, "Couldn't prepare statement: " . $dbh_ora->errstr);
$sel->execute;
my $ins = $dbh_pg->prepare($target)
or elog(ERROR, "Couldn't prepare statement: " . $dbh_pg->errstr);
my $fetch_tuple_sub = sub { $sel->fetchrow_arrayref };
my @tuple_status;
my $rc = $ins->execute_for_fetch($fetch_tuple_sub, \(at)tuple_status);
if (DBI->err) {
elog(INFO, DBI->errstr."\n");
my @errors = grep { ref $_ } @tuple_status;
foreach my $error (@errors) {
elog(INFO, $error->[1]);
}
}
$dbh_ora->disconnect;
$dbh_pg->disconnect;
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
And this ... well you can guess...
select dbi_insert3();
Thanks for any help!