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=firev12 0-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.

On Wed, May 30, 2007 at 09:33:40AM +0200, Bart Degryse wrote:
> 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?

In PL/Perl functions you can use eval to catch errors just as you
would in an ordinary Perl script:

eval { do something };
if ($@) { handle the error }

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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=firev12 0-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!

"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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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