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 function needs 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