Tom Lane wrote:
Ezequiel Tolnay <[EMAIL PROTECTED]> writes:Please note that the critical issue is a column defined as numeric(15,4) (through the domain alias), but still capable of containing numbers with higher precision. I wonder how is it possible to store a numeric with higher precision on a column where the precision is clearly defined. Are the numeric values stored on a column always variable precision, and the precision being only enforced on assignment? Wouldn't this impact heavily in performance and disk space usage?
CREATE DOMAIN currency AS numeric(15,4);
CREATE TABLE test (id serial, amt currency);
CREATE FUNCTION f_test(currency) RETURNS currency AS $$
DECLARE n currency;
BEGIN n := $1 * 0.2::float4;
INSERT INTO test (amt) VALUES (n); RETURN n;
END $$ LANGUAGE PLPGSQL;
plpgsql doesn't currently enforce domain constraints, so the assignment to n isn't doing the rounding that you expect. Until someone gets around to fixing that, an explicit coercion is probably what you need:
n := cast($1 * 0.2::float4 AS currency);
In any case, it looks like the only flaw is when inserting or updating with a plpgsql variable into a column defined as a domain for a fixed precision numeric.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
