perlu: did I find a bug, or did I make one? - Mailing list pgsql-sql

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:

And this is what I expect to get:

 

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, \@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!