The following bug has been logged online: Bug reference: 5717 Logged by: Richard Huxton Email address: d...@archonet.com PostgreSQL version: 9.0.1 Operating system: linux Description: Domain as array of numeric/varchar does not respect limits Details:
Summary: you can insert numbers that are outside the numeric(n,m) restrictions via a function's return value *iff* the numbers are elements of an array. This does not apply to a single numeric. A similar issue applies to varchar lengths. The only route appears to be through the return value of an array. Presumably the system trusts the value to be restricted to the domain when it isn't. The following allows (and displays) {121.0000} and {0.0001} in a column defined as numeric(4,2)[1]. BEGIN; CREATE DOMAIN mynums numeric(4,2)[1]; CREATE TEMP TABLE tt(n mynums); CREATE TEMP TABLE tt2(n numeric[1]); CREATE FUNCTION mul_num(n mynums) RETURNS mynums AS $$ DECLARE n2 mynums; i integer; BEGIN n2[1] := n[1] * n[1]; RETURN n2; END; $$ LANGUAGE plpgsql; INSERT INTO tt VALUES (ARRAY[1]); SELECT * FROM tt; \echo \echo 'This should not work' \echo INSERT INTO tt SELECT mul_num(ARRAY[11]); INSERT INTO tt SELECT mul_num(ARRAY[0.01]); SELECT * FROM tt; \echo \echo 'This fails, which is what I expect' \echo SAVEPOINT s1; INSERT INTO tt VALUES (ARRAY[121]); ROLLBACK TO s1; INSERT INTO tt2 VALUES (ARRAY[121]); INSERT INTO tt SELECT n FROM tt2; ROLLBACK; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs