The following bug has been logged online:
Bug reference: 5717
Logged by: Richard Huxton
Email address: dev@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;
