Re: Trapping errors

On 23 May 2011, at 22:08, Shane W wrote:

> Hello list,
>
> I have a table with double precision columns and update
> queries which multiply and divide these values. I am
> wondering if it's possible to catch overflow and underflow
> errors to set the column to 0 in the case of an underflow
> and a large value in the case of an overflow.
>
> Currently, I have an exception handler in a PLPGSQL
> ufunction that sort of does this.
>
> begin
> update tbl set score = score/s
> exception when numeric_value_out_of range then
> update tbl set score=0
> where cast(score/s as numeric) < 1e-200
> end;
>
> But this is messy since the exception needs to rescan the
> entire table if even one row fails the update. Is there a
> better way to do this?

If you move the overflow/underflow check into a before-trigger, then you can use the NEW.* and OLD.* variables to alter the row before it gets written. That way you scan the table only once and you also moved your handling of such errors into the database (which means that if other applications than your client ever write values to that table, the same rules are applied).

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4ddb6e8111921119526771!