Re: SQL:2011 application time

On Tue, Jan 9, 2024 at 2:54 AM Paul Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> On 1/8/24 06:54, jian he wrote:
> > On Fri, Jan 5, 2024 at 1:06 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> >
> > range_intersect returns the intersection of two ranges.
> > I think here we are doing the opposite.
> > names the main SQL function "range_not_intersect" and the internal
> > function as "range_not_intersect_internal" should be fine.
> > so people don't need to understand the meaning of "portion".
>
> Thank you for helping me figure out a name here! I realize that can be a bike-sheddy kind of
> discussion, so let me share some of my principles.
>
> Range and multirange are highly mathematically "pure", and that's something I value in them. It
> makes them more general-purpose, less encumbered by edge cases, easier to combine, and easier to
> reason about. Preserving that close connection to math is a big goal.
>
> What I've called `without_portion` is (like) a closed form of minus (hence `(at)-` for the operator).
> Minus isn't closed under everything (e.g. ranges), so `without_portion` adds arrays---much as to
> close subtraction we add negative numbers and to close division we add rationals). We get the same
> effect from multiranges, but that only buys us range support. It would be awesome to support
> arbitrary types: ranges, multiranges, mdranges, boxes, polygons, inets, etc., so I think an array is
> the way to go here. And then each array element is a "leftover". What do we call a closed form of
> minus that returns arrays?
>
> Of course "minus" is already taken (and you wouldn't expect it to return arrays anyway), which is
> why I'm thinking about names like "without" or "except". Or maybe "multi-minus". I still think
> "without portion" is the closest to capturing everything above (and avoids ambiguity with other SQL
> operations). And the "portion" ties the operator to `FOR PORTION OF`, which is its purpose. But I
> wouldn't be surprised if there were something better.
>

Thanks for the deep explanation. I think the name
range_without_portion is better than my range_not_intersect.
I learned a lot.
I also googled " bike-sheddy". haha.

src5=# select range_without_portion(numrange(1.0,3.0,'[]'),
numrange(1.5,2.0,'(]'));
range_without_portion
---------------------------
{"[1.0,1.5]","(2.0,3.0]"}
(1 row)

src5=# \gdesc
Column | Type
-----------------------+-----------
range_without_portion | numeric[]
(1 row)

src5=# \df range_without_portion
List of functions
Schema | Name | Result data type | Argument data
types | Type
------------+-----------------------+------------------+---------------------+------
pg_catalog | range_without_portion | anyarray | anyrange,
anyrange | func
(1 row)

so apparently, you cannot from (anyrange, anyrange) get anyarray the
element type is anyrange.
I cannot find the documented explanation in
https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

anyrange is POLYMORPHIC, anyarray is POLYMORPHIC,
but I suppose, getting an anyarray the element type is anyrange would be hard.