Re: function within a function/rollbacks/exception handling

Hello

2011/11/7 Lori Corbani <Lori(dot)Corbani(at)jax(dot)org>:
>
> 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(....)'?
>
> 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)?

PostgreSQL function does not do rollback by self. You should to do it
manually after exception. When functionMain fails, then trigger fails
too.

If trigger contains a exception handler, then it start a
subtransaction on begin block execution and rollback this
subtransaction when exception is raised.

Regards

Pavel Stehule

>
> Thanks.
> Lori
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>