From: Mitch Vincent <mitch@venux.net>
To: pgsql-sql@postgreSQL.org
Subject: numeric question..
Date: Fri, 14 Jan 2000 11:56:36 -0500
Message-ID: <009b01bf5eb0$51d609e0$40ee2fd8@venux.net> (raw)
References: <Pine.GSO.3.96.1000113235142.2456A-100000@sanmarino.tcsi.com>
<29298.947862153@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mitch@venux.net
Subject: Re: numeric question..
In-Reply-To: <009b01bf5eb0$51d609e0$40ee2fd8@venux.net>
References: <Pine.GSO.3.96.1000113235142.2456A-100000@sanmarino.tcsi.com> <29298.947862153@sss.pgh.pa.us> <009b01bf5eb0$51d609e0$40ee2fd8@venux.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox