RAISE NOTICE won't help (I don't think) because the notice isn't visible from inside the C++ application, so the user won't know that a problem occured.
I think I just came up with a thoroughly ugly idea. The database supports an annealing shop, in which coils are assigned to charges. After the check fails, I end up with coils assigned to a charge that does not exist. I could set up a job that runs every minute and checks all coils with status "Assigned" to make sure that the associated charges actually exist. That would fix another recurring problem, in which a user intentionally deletes a charge but the charge's coils stay assigned to that charge. We haven't tracked that down yet, but this job would fix that too.
Now all I have to do is learn how to set up a job. I only know about them from overhearing colleagues talking.
RobR
From: Jaime Casanova [mailto:jcasanov@systemguards.com.ec]
Sent: Thu 10/2/2008 10:11 PM
To: Alvaro Herrera
Cc: Rob Richardson; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I save data and then raise an exception?
On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera
<alvherre@commandprompt.com> 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.
>
what about RAISE NOTICE?
--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
