pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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










view thread (6+ messages)  latest in thread

Message-ID: <009b01bf5eb0$51d609e0$40ee2fd8@venux.net>
Permalink:  ../009b01bf5eb0$51d609e0$40ee2fd8@venux.net/
Also on:    postgresql.org/message-id/009b01bf5eb0$51d609e0$40ee2fd8@venux.net

 · 

reply

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