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.