Обсуждение: Argument casting hierarchy?
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.
I appreciate it.
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
What is it you're showing me exactly? The valid numeric types accepted (and cast) by both real and numerics?
On Mon, Jul 1, 2019 at 1:15 PM Adrian Klaver <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:wellsoliver@gmail.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.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)