UPDATE tbl SET score = divide_double_default(score, s, 1e-200) ...
UPDATE tbl SET score = multiply_double_default(score, s, 999999999) ...
Code the divide_double_default/multiply_double_default functions with error handling that will return the desired value
(eitherzero or the supplied parameter) if an exception is thrown; probably with a WARNING/NOTICE raised as well.
David J.
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Shane W
> Sent: Monday, May 23, 2011 4:08 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Trapping errors
>
> 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?
>
> Best,
> Shane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
