Обсуждение: range of composite types!
range of composite types. I found this would be a great idea!!!
source code regress test 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::date from 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.
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::date from 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.