Thread: Argument casting hierarchy?

On 7/1/19 12:18 PM, Wells Oliver wrote:
> Hi guys, hoping you can help clarify what the 'hierarchy' of casts might 
> be in function arguments.
> 
> Meaning I have the following two functions
> 
> stats.foo1 (a bigint, b bigint) returns a/b::numeric
> 
> stats.foo1 (a real, b real) returns a/b::numeric
> 
> It's essentially the same function, but I thought I had to create two to 
> accept the different types.
> 
> However, when I call stats.foo1(3::smallint, 4::smallint) I receive the old:
> 
> ERROR:  function stats.foo(smallint, smallint) is not unique
> 
> Along w/ the message to explicitly cast.
> 
> I am essentially trying to write one function that will cast "down", I 
> guess, anything smaller than what's passed. I am guessing I only need 
> the real/real argument signature, but I wanted some clarity from the group.

 From the system catalogs, where float4 = real:

select typname AS cast_target  from pg_cast join pg_type on 
pg_cast.casttarget = pg_type.oid  where castsource = 'float4'::regtype;
  cast_target
-------------
  int8
  int2
  int4
  float8
  numeric

select typname AS cast_target  from pg_cast join pg_type on 
pg_cast.casttarget = pg_type.oid  where castsource = 'numeric'::regtype;
  cast_target
-------------
  int8
  int2
  int4
  float4
  float8
  money
  numeric
(7 rows)



> 
> I appreciate it.
> 
> -- 
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/1/19 1:18 PM, Wells Oliver wrote:
> What is it you're showing me exactly? The valid numeric types accepted 
> (and cast) by both real and numerics?

That there is builtin casting for a source of either float4 or numeric 
to the types listed respectively and that numeric offers more choices. 
Might help you decide on which type you want to use for a single function.

> 
> On Mon, Jul 1, 2019 at 1:15 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/1/19 12:18 PM, Wells Oliver wrote:
>      > Hi guys, hoping you can help clarify what the 'hierarchy' of
>     casts might
>      > be in function arguments.
>      >
>      > Meaning I have the following two functions
>      >
>      > stats.foo1 (a bigint, b bigint) returns a/b::numeric
>      >
>      > stats.foo1 (a real, b real) returns a/b::numeric
>      >
>      > It's essentially the same function, but I thought I had to create
>     two to
>      > accept the different types.
>      >
>      > However, when I call stats.foo1(3::smallint, 4::smallint) I
>     receive the old:
>      >
>      > ERROR:  function stats.foo(smallint, smallint) is not unique
>      >
>      > Along w/ the message to explicitly cast.
>      >
>      > I am essentially trying to write one function that will cast
>     "down", I
>      > guess, anything smaller than what's passed. I am guessing I only
>     need
>      > the real/real argument signature, but I wanted some clarity from
>     the group.
> 
>       From the system catalogs, where float4 = real:
> 
>     select typname AS cast_target  from pg_cast join pg_type on
>     pg_cast.casttarget = pg_type.oid  where castsource = 'float4'::regtype;
>        cast_target
>     -------------
>        int8
>        int2
>        int4
>        float8
>        numeric
> 
>     select typname AS cast_target  from pg_cast join pg_type on
>     pg_cast.casttarget = pg_type.oid  where castsource = 'numeric'::regtype;
>        cast_target
>     -------------
>        int8
>        int2
>        int4
>        float4
>        float8
>        money
>        numeric
>     (7 rows)
> 
> 
> 
>      >
>      > I appreciate it.
>      >
>      > --
>      > Wells Oliver
>      > wells.oliver@gmail.com <mailto:wells.oliver@gmail.com>
>     <mailto:wellsoliver@gmail.com <mailto:wellsoliver@gmail.com>>
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 
> -- 
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

>>>>> "Wells" == Wells Oliver <wells.oliver@gmail.com> writes:

 Wells> Hi guys, hoping you can help clarify what the 'hierarchy' of
 Wells> casts might be in function arguments.

In terms of which casts will be selected in some given context, there
isn't exactly a "hierarchy", but some types are designated as
"preferred" types in their category. Of the builtin types, the preferred
ones are boolean, text, oid, double precision, inet, timestamptz,
interval and varbit. (See pg_type.typispreferred and
pg_type.typcategory.)

If there are several ways to implicitly cast function arguments to match
signatures of known functions, then the result is ambiguous (and hence
an error) _unless_ there is one (and only one) combination of casts to
preferred types in matching categories. So in your example, if you had a
variant of the function (a float8, b float8) then smallint inputs would
call that variant, in preference to the bigint or real ones, because
float8 (aka double precision) is a preferred type in category N (number
types).

You have to be a bit careful with this because it sometimes leads to
unexpected casts or loss of precision. If your function will be casting
the input values to "numeric", for example, then it is better to have
just one function with numeric type args, rather than risk getting casts
like numeric -> float8 -> numeric. Another commonly seen example is the
mis-casting of "date" type to timestamptz (as the preferred type) where
timestamp without tz was intended or semantically required.

-- 
Andrew (irc:RhodiumToad)