Thread: 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
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
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 therow before it gets written. That way you scan the table only once and you also moved your handling of such errors intothe database (which means that if other applications than your client ever write values to that table, the same rulesare applied). Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4ddb6e8111921119526771!