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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ezequiel Tolnay <mail(at)etolnay(dot)com(dot)ar>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Unexpected behaviour of numeric datatype when mixed with, float4, domains and plpgsql variables
Date: 2005-04-10 21:18:14
Message-ID: 16029.1113167894@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Ezequiel Tolnay <mail(at)etolnay(dot)com(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2005-04-10 21:28:38 Re: BUG #1584: undefined symbol _tas
Previous Message Tom Lane 2005-04-10 21:00:14 Re: BUG #1591: BETWEEN NULL AND NULL causes crash