Re: Precision/scale of a numeric attribute of a new data type are not handled correctly when the type is returned by a function

Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu> writes:
> create type public.test_type as (  value1 numeric(35,6),     value2
> numeric(35,6) );

> create or replace function public.test(    )    returns test_type   
> language  'sql'  as $body$   select 7.136178319899999964,
> 7.136178319899999964;  $body$;

> select value1, value2 from test();
> Expected:  7.136178 7.136178
> Actual:  7.136178319899999964 7.136178319899999964

Hm. In general, this isn't inconsistent with the rule that the
output of a function doesn't have any particular typmod
(unless it's a length-coercion function). However, it's certainly
not good that you then get

> select value1::numeric(35,6), value2::numeric(35,2) from test();
> Expected:  7.136178 7.14
> Actual: 7.136178319899999964 7.14

showing that some part of the system does believe that the output
columns have a particular typmod, and hence don't need further
coercion.

Blame for this might be laid on check_sql_fn_retval(), which
pays no particular attention to typmods. However, I don't think
it'd be reasonable to fix it by just extending that function's
existing behavior to insist on typmod as well as type match.
That would cause this example to throw an error until you put
explicit coercions onto the constants; which I bet nobody would
love us for.

A better idea, perhaps, is to get rid of the insistence that
the outputs of a SQL function be exactly of the target type
(modulo binary compatibility, which is a concept that has no
place in user-visible semantics anyway). If we had the ability
to insert coercion functions, we could allow any case where
there's an implicit (or, perhaps, assignment) coercion defined,
including applying a length coercion if needed.

This seems like it'd require some nontrivial surgery in
functions.c, though. The code path wherein check_sql_fn_retval()
is allowed to modify the tlist wouldn't be so hard to fix, but
that only applies for inlining transformations. The main SQL
function execution engine isn't prepared to do anything smarter
than applying a junkfilter to the query outputs, and we'd have
to change that. Maybe drop the junkfilter bit entirely in favor
of plastering another projection step atop the finished plan?

regards, tom lane