Thread: PL/pgSQL

Below is the info from the on-line docs for 7.4.2.  Can someone tell me how
to hook into the error mechanism to notice that this happens. Like the doc
says you can.

I want to do something special in PL/pgSQL if I notice an error has been
encountered. Like log to a table. I know you could do this in the calling
language C++,VB or whatever your app is written in. I would just like to
have this implemented in PL/pgSQL for many reasons.  Like Oracle, Firebird,
Interbase and everyone else does.

If PL/pgSQL had that I would be 100% happy with PostgreSQL instead of 99%
happy(not that anyone cares about my happiness). That's why I'm willing to
do something about this if I can only get some help and guidance on doing
this.   Any  coders out there want to help me out on figuring this out or
adding this to the PostgreSQL source for all to use and enjoy?  To me this
is always that one thing that PostgerSQL doesn't have that I think is not a
plus but a needed piece of functionality.

Thanks



23.8.1. Exceptions


PostgreSQL does not have a very smart exception handling model. Whenever
the parser, planner/optimizer or executor decide that a statement cannot be
processed any longer, the whole transaction gets aborted and the system
jumps back into the main loop to get the next query from the client
application.


It is possible to hook into the error mechanism to notice that this
happens. But currently it is impossible to tell what really caused the
abort (input/output conversion error, floating-point error, parse error).
And it is possible that the database backend is in an inconsistent state at
this point so returning to the upper executor or issuing more commands
might corrupt the whole database.


Thus, the only thing PL/pgSQL currently does when it encounters an abort
during execution of a function or trigger procedure is to write some
additional NOTICE level log messages telling in which function and where
(line number and type of statement) this happened. The error always stops
execution of the function.


Bob Henkel          651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125




*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************

On Wednesday 31 March 2004 18:58, Bob.Henkel@hartfordlife.com wrote:
> Below is the info from the on-line docs for 7.4.2.  Can someone tell me how
> to hook into the error mechanism to notice that this happens. Like the doc
> says you can.

If what you want to do is detect errors and work around them, this will
require nested transactions.

> If PL/pgSQL had that I would be 100% happy with PostgreSQL instead of 99%
> happy(not that anyone cares about my happiness).

The core care about all our happiness :-)
But there aren't that many of them :-(
But I do know there's interest in getting nested transactions working :-)
But it's not ready yet :-(
But you can help :-)

> That's why I'm willing to
> do something about this if I can only get some help and guidance on doing
> this.   Any  coders out there want to help me out on figuring this out or
> adding this to the PostgreSQL source for all to use and enjoy?

Firstly - good for you. People like you are the reason PG is such a pleasure
to work with.

Secondly, check the pgsql-hackers list (start with the archives). I don't know
what the state of nested-transactions is, or whether you have enough C
knowledge to help out at this stage.

If you can't help with the code, but you can sync CVS and compile from source,
then you can help testing.

If you don't feel comfortable with that, then there will need to be
documentation written (later) and examples, and articles that can go on
postgresql.org or news sites.

> To me this
> is always that one thing that PostgerSQL doesn't have that I think is not a
> plus but a needed piece of functionality.

I'm sure you'll be able to move this feature along at some point - if like me,
you lack the C skills to code, you may have to be patient though.

--
  Richard Huxton
  Archonet Ltd