Received: from redbox.venux.net (redbox.venux.net [216.47.238.10]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA24796 for ; Fri, 14 Jan 2000 11:53:26 -0500 (EST) (envelope-from mitch@venux.net) Received: from inky (inky.venux.net [216.47.238.64]) by redbox.venux.net (Postfix) with SMTP id 3106C2E227 for ; Fri, 14 Jan 2000 11:45:50 -0500 (EST) Message-ID: <009b01bf5eb0$51d609e0$40ee2fd8@venux.net> From: "Mitch Vincent" To: References: <29298.947862153@sss.pgh.pa.us> Subject: numeric question.. Date: Fri, 14 Jan 2000 11:56:36 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 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