range of composite types!

Hello.
Just wondering if this is possible or not..

---------- Forwarded message ---------
From: Jian He <hejian.mark@gmail.com>
Date: Tue, Apr 26, 2022 at 2:46 PM
Subject: range of composite types!
To: pgsql-general <pgsql-general@lists.postgresql.org>

range of composite types. I found this would be a great idea!!!
Question on stackoverflow
<https://stackoverflow.com/questions/71996169/some-of-range-composite-type-operator-only-check-the-elements-of-composite-type&gt;
DB Fiddle
<https://dbfiddle.uk/?rdbms=postgres_14&amp;fiddle=cdffa53650e8df576bc82d0ae2e1beef&gt;

source code regress test
<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/sql/rangetypes.sql;h=b69efede3ae4977e322c1349c02a5dc2f74b7cc4;hb=6df7a9698bb036610c1e8c6d375e1be38cb26d5f&gt;
ranges of composite types code part:

504 --

505 -- Ranges of composites
506 --
507
508 create type two_ints as (a int, b int);
509 create type two_ints_range as range (subtype = two_ints);
510
511 -- with force_parallel_mode on, this exercises tqueue.c's range
remapping
512 select *, row_to_json(upper(t)) as u from
513 (values (two_ints_range(row(1,2), row(3,4))),
514 (two_ints_range(row(5,6), row(7,8)))) v(t);

-- composite type range.

create type mytype as (t1 int, t2 date);
-- create type my_interval as (t1 int, t2 interval);
select (2,'2022-01-02')::mytype ;
create type mytyperange as range(subtype = mytype);

I am thinking construct a composite type range that would be equivalent as:

select a, b::datefrom generate_series(1,8) a,
generate_series('2022-01-01'::timestamp,
'2022-01-31'::timestamp, interval '1 day') b;

for that means the following sql queries should return* false:*

select mytyperange (

(1,'2022-01-01')::mytype,
(8, '2022-01-31')::mytype, '[]') @> (2, '2020-01-19')::mytype;

select
(2, '2020-01-19')::mytype <@
mytyperange(
(1,'2022-01-01')::mytype,
(8, '2022-01-31')::mytype, '[]') ;

--does the range overlaps, that is, have any common element.
select
mytyperange ((2,'2020-12-30')::mytype,
(2, '2020-12-31')::mytype)
&&
mytyperange(
(1,'2022-01-01')::mytype,
(8, '2022-01-31')::mytype) ;

from the db fiddle link, so far I failed.
If this is possible then we may need a *subtype_diff *function and *canonical
*function.

Jian He <hejian.mark@gmail.com> writes:

for that means the following sql queries should return* false:*

select mytyperange (
(1,'2022-01-01')::mytype,
(8, '2022-01-31')::mytype, '[]') @> (2, '2020-01-19')::mytype;

Why should that return false? The comparison rules for composites
say that you compare the first column, only if that's equal
compare the second, etc. Here, "2" is between "1" and "8" so
the contents of the second column don't matter.

regards, tom lane

On Tuesday, April 26, 2022, Jian He <hejian.mark@gmail.com> wrote:

-- composite type range.

create type mytype as (t1 int, t2 date);
-- create type my_interval as (t1 int, t2 interval);
select (2,'2022-01-02')::mytype ;
create type mytyperange as range(subtype = mytype);

I am thinking construct a composite type range that would be equivalent
as:

select a, b::datefrom generate_series(1,8) a,
generate_series('2022-01-01'::timestamp,
'2022-01-31'::timestamp, interval '1 day') b;

Ranges have to be ordered. How do you propose to order the above?

Composite type comparisons have defined ordering semantics. Your results
demonstrate what those are (namely, subsequent fields are used only to
break ties). If you want different behavior you will have to code it
yourself - possibly including ignoring the generic composite type
infrastructure and make a formal base type of whatever it is you need.

David J.