pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedspi and error messages
2+ messages / 2 participants
[nested] [flat]
* spi and error messages
@ 2007-05-30 07:33 Bart Degryse <Bart.Degryse@indicator.be>
2007-05-30 14:13 ` Re: spi and error messages Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Bart Degryse @ 2007-05-30 07:33 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: spi and error messages
2007-05-30 07:33 spi and error messages Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-05-30 14:13 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2007-05-30 14:13 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-05-30 14:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-30 07:33 spi and error messages Bart Degryse <Bart.Degryse@indicator.be>
2007-05-30 14:13 ` Michael Fuhr <mike@fuhr.org>
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