From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: spi and error messages
Date: Wed, 30 May 2007 09:33:40 +0200
Message-ID: <465D44F3.A3DD.0030.0@indicator.be> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: spi and error messages
In-Reply-To: <465D44F3.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox