Re: Improve upcasting for INT range and multi range types

Hi,

Thanks for the reply. I suspected that there were technical reasons
that prevented the obvious right thing to be done.

Would adding overloads to the functions and operators be something
that could be considered as an acceptable solution?
I've tried a very naive solution and it seems to work (there are for
sure better options to declare the function overloads):

    begin;

    create function elem_contained_by_range(int4, int8range) returns
boolean as $$ select elem_contained_by_range($1::int8, $2) $$ LANGUAGE
SQL;
    create function elem_contained_by_range(int8, int4range) returns
boolean as $$ select elem_contained_by_range($1, $2::text::int8range)
$$ LANGUAGE SQL;

    create operator <@(
        LEFTARG = int4,
        RIGHTARG = int8range,
        FUNCTION = elem_contained_by_range,
        RESTRICT = rangesel,
        JOIN = contjoinsel,
        HASHES, MERGES
    );
    create operator <@(
        LEFTARG = int8,
        RIGHTARG = int4range,
        FUNCTION = elem_contained_by_range,
        RESTRICT = rangesel,
        JOIN = contjoinsel,
        HASHES, MERGES
    );

    select 2::int4 <@ '[1,9)'::int8range;
    select 2::int8 <@ '[1,9)'::int4range;

    rollback;

The major drawback is that every combination operator - type would
need its own overload creating a large number of them.

As a side note it seems that int4range cannot be casted automatically
to int8range.

Best regards,
    Federico

On Wed, 13 Dec 2023 at 05:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> jian he <jian.universality@gmail.com> writes:
> > Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1,
> > 4)'::INT8RANGE is doable.
>
> Yeah, it would require a considerable expansion of the scope of
> what can be matched by a polymorphic operator.  I'm afraid that
> the negative consequences (mainly, "ambiguous operator" failures
> because more than one thing can be matched) would outweigh the
> benefits.  It is kind of annoying though that the system can't
> do the "obvious" right thing here.
>
>                         regards, tom lane