Re: How to track exceptions in PL/pgSQL

On Wed, Oct 12, 2005 at 12:40:29PM +0200, Florian Ledoux wrote:
> I would like to track (timestamp and error number) in the database or
> in text files the exceptions that occur in my PL/pgSQL functions.
>
> 1. How can I retrieve the SQLCODE in a 8.0.3 PG server ?

You can't, at least not that I'm aware of.  In 8.1 you'll have
SQLSTATE and SQLERRM but they're not available in earlier versions.
I don't know if you could take the source code for 8.1's PL/pgSQL
and build it under 8.0; if not, then you might at least be able to
isolate the SQLSTATE/SQLERRM code and add it to 8.0's PL/pgSQL.
Perhaps one of the developers will comment about how feasible this
would be.

Without SQLSTATE/SQLERRM you could use several "WHEN some_exception
THEN" clauses to trap the most likely exceptions so at least you'd
know what kind of exception you were handling.

> 2. How can I trace the exception in a table although an implicit
> rollback is done when a exception occurs (the trace will be rollbacked
> too) ?

You should be able do inserts from the handler code.  It worked for
me in simple tests, so apparently the rollback is done before the
handler code is executed.

--
Michael Fuhr