Re: function within a function/rollbacks/exception handling

On 07/11/11 19:18, Lori Corbani wrote:
>
> I have a function, call it 'functionMain'.  And I have several tables that each have trigger functions.  Each trigger
functionneeds to call 'functionMain' (with different parameters). 
>
> table A =>  trigger function A ==>  functionMain
> table B =>  trigger function B ==>  functionMain
> table C =>  trigger function C ==>  functionMain
>
> 'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block.
>
> An insert transaction for table A is launched (insertA), trigger function A is called,
> 'functionMain' is called and 'functionMain' fails.  Hence, trigger function A needs to rollback.
>
> Questions:
>
> a) I am assuming that the trigger functions should use 'PERFORM functionMain(....)'?

If you don't want the result, yes.

> b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct?
>
> c) if 'functionMain' fails, should the trigger function also contain an exception handler
>     or will the rollback from 'functionMain' cascade up to the original transaction (insertA)?

Unless you catch the exception, it will roll back the whole transaction,
so "yes" to b + c. If it helps to visualise what happens, exceptions are
actually implemented using savepoints in plpgsql.

--
   Richard Huxton
   Archonet Ltd