Re: Argument casting hierarchy?

[Wells Oliver]

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 
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.oli...@gmail.com <mailto:wellsoli...@gmail.com>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
Wells Oliver
wells.oli...@gmail.com 

Argument casting hierarchy?

[Wells Oliver]

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.

-- 
Wells Oliver
wells.oli...@gmail.com 

Querying w/ join slow for large/many child tables

[Wells Oliver]

I have a primary parent table with a child table per week of the year for
each week back through 2015. There are a lot of child tables. Each week's
child table has maybe  80-110m rows.

When I join to the parent table on a column, it's very slow, but when I
manually specify the specific week's child table, it's quite fast, e.g.

Slow:
select * from foo
join schema.mytable on foo.col = mytable.col

vs. fast:
select * from foo
join schema.mytable_2015_wk33 as mytable on foo.col = mytable.col

What's the resolution to make querying the table faster in terms of it
finding the appropriate child table? Is it putting an index on `col` on
each child table? Some other thing?

Thank you.

-- 
Wells Oliver
wells.oli...@gmail.com