Re: Unexpected behaviour of numeric datatype when mixed with,float4, domains and plpgsql variables

Ezequiel Tolnay <mail@etolnay.com.ar> writes:
> 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);

Keep in mind also that declaring a function result value as a domain
is pretty dangerous, because none of the PLs enforce domain constraints
on their results.

            regards, tom lane