Re: How do I save data and then raise an exception?

["Gurjeet Singh" <singh.gurjeet@xxxxxxxxx>]

On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> wrote:

Rob Richardson wrote:

> Here's what I need to do:
>
> IF query_check_fails THEN
>     UPDATE some_table SET some_value = 0 WHERE some_condition_is_true;
>     RAISE EXCEPTION 'Look, you idiot, do it right next time!';
> END;
>
> I need the update to work, but I need to raise the exception so the C++
> code recognizes the error.  How can I do both?

You need an autonomous transaction, which Postgres does not support
directly but you can implement using dblink or a plperl function that
connects back to the database.

I was also going to suggest that but did not, because autonomous transaction won't help here! The data has been INSERTed or UPDATEd in this transaction, and hence won't be visible to the autonomous transaction, because the main transaction hasn't committed yet.

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device