The following bug has been logged on the website:
Bug reference: 10703
Logged by: Tomasz Pala
Email address: gotar@polanet.pl
PostgreSQL version: 9.1.3
Operating system: Linux
Description:
Hello,
recently I've found an issue with casting value returned by SRF, which could
be easily reproduced by:
=> CREATE TABLE a (a numeric(7,2));
=> create function a() returns setof a as 'select pi()' language sql;
ERROR: return type mismatch in function declared to return a
DETAIL: Final statement returns double precision instead of numeric at
column 1.
CONTEXT: SQL function "a"
that's great, until now everything works as one might expect, but let's try
to override this:
=> create function a() returns setof a as 'select 3.14/10' language sql;
=> SELECT * from a();
0.31400000000000000000
Apparently there were no automatic casts added, so there is no sanity check
in effect. But OK, one might assume that he gets what he wanted. The weird
stuff comes next:
=> select a::numeric(7,2) from a();
0.31400000000000000000
=> select a::numeric(7,3)::numeric(7,2) from a();
0.31
The problem is: if PostgreSQL doesn't ensure SRF returns proper type (by
doing automatic casting) then it shouldn't omit explicit casting even to the
same type as assumed to be returned. In current state any "cautious"
programmer might be mislead into thinking he would get the type he asks for
(via casting), while in some circumstances he might not (unless he uses two
consecutive casts like in example above).
I'm not sure if this behaviour could be considered a bug, or just another
funny thing that experienced programmer should be aware of:)
best regards