Thread: spi and error messages

Hi,

I'm writing some function to fetch data from an Oracle database and store it in a PostgreSQL database.

 

CREATE OR REPLACE FUNCTION public.replicate_billing(text, date, date) RETURNS void AS
$body$
  use DBI;
  my $dbh_ora = DBI->connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars', 'bmssa', '***********', {PrintError => 0});
  elog(ERROR, DBI->errstr) unless ($dbh_ora);
  my $query = 'SELECT ... FROM ... WHERE ...';
  my $sel = $dbh_ora->prepare($query);
  elog(ERROR, $dbh_ora->errstr);
  sel->execute;
  elog(ERROR, $dbh_ora->errstr);
  my $target = 'INSERT INTO ... VALUES ($1,$2,$3)';
  my $plan = spi_prepare($target, 'varchar', 'varchar', 'date');
  elog(ERROR, ???????);

  ...

  spi_freeplan($plan);

$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

As you can see I raise an error if connecting to Oracle fails and if preparing or executing the plan for fetching data fails.

Likewise I would like to raise an error if preparing the insert statement fails. As error message I would like to use the message generated by postgresql itself just like I do in the Oracle part of my function. I can't seem to find however how to do that.

In general how should I catch the error message generated if one of the spi functions (spi_exec_query, spi_query, spi_fetchrow, spi_prepare,...) fails?

 

Thanks for your help.

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!

Well, actually I do. If there's any error, I want nothing done.

But my real point was that although there are 2 records in my source table with dataareaid = 'lil' and two with dataareaid = 'bol' I still get 4 times the 'lil' error message, while I was expecting 2 times the 'lil' error message and two times the 'bol' error message.

>>> Tom Lane <tgl@sss.pgh.pa.us> 2007-06-04 16:52 >>>
"Bart Degryse" <Bart.Degryse@indicator.be> writes:
> 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 =3D 'lil' THEN
>     RAISE EXCEPTION '% foutje %', NEW.dataareaid, NEW.name;
>   elsIF NEW.dataareaid =3D '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;

You probably don't want this trigger doing RETURN NULL; that's
turning all your inserts into no-ops.

regards, tom lane