I have a table (invoices) that have several fields for various charges,
these fields are of type numeric(9,2).
I have a function and a trigger to total these fields on every update or
insert and place the value in the total field of the respective invoice.
CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
BEGIN NEW.total := NEW.fee_membership + NEW.fee_logins + NEW.fee_convention +
NEW.fee_prints_jobs + NEW.fee_prints_apps + NEW.fee_hotlines + NEW.fee_postage + NEW.fee_ups +
NEW.fee_late + NEW.fee_other1 + NEW.fee_other2 + NEW.fee_other3 + NEW.fee_pastdue -
NEW.amount_paid; RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER invoice_total_trigger BEFORE INSERT OR UPDATE ON invoice
FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();
Now, this all worked fine with the fields being type float8 but I didn't
want to have to pad the numbers on display for formatting, so I decided to
use the numeric data type so that postgres would automatically store numbers
like 100 as 100.00.... Anyway, when I insert or update and the trigger calls
the function, I get this error :
Query Failed
ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 0
scale 2
Several of the fields are over 100.00, this isn't a problem if I remove the
trigger/function and insert into the other data tables values way over
100.00 so the problem is within the function I guess...
It's simple addition and subtraction, is this a problem for the numeric
type? If it's something else small and obvious, feel free to flame me until
I die from it :-)
Thanks!!!!
-Mitch
