I'm experience some strange behaviour when casting numeric values.

Given the following SQL-Statement:

SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();

Generated output is:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632

Expected output was:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | --> 8.63 | 8.632



reproducable testcase:

CREATE TABLE numtest (
n NUMERIC(9,2),
i INTEGER
);
INSERT INTO numTest VALUES (13163.38000, 1525);

CREATE TABLE frslt_test (
a_int INTEGER,
a_num NUMERIC(9,2)
);

CREATE OR REPLACE FUNCTION f_numTest() RETURNS SETOF frslt_test AS '
SELECT CAST (SUM(i) AS INTEGER),
SUM(n)/SUM(i)
FROM Numtest
' LANGUAGE 'sql';

SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();

select version();

a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632

version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu4)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sven Welte <Sven.Welte (AT) gmxpro (DOT) net> writes:
This doesn't really have anything to do with casting as such. The
system thinks it can throw away the cast to NUMERIC(9,2) because the
function result is declared as already NUMERIC(9,2) ... but the function
is not actually constraining its result that way. In general a function
returning NUMERIC is considered to return unconstrained NUMERIC (and
likewise for VARCHAR etc).

Possibly we should disallow the length decoration on columns used in a
function result typedef, because it fools people into thinking that such
a decoration will be enforced.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend