Thread: Fwd: range of composite types!

Fwd: range of composite types!

From

Jian He

Date:

27 April 2022, 08:03:49

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

DB Fiddle

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.

Re: Fwd: range of composite types!

From

Tom Lane

Date:

27 April 2022, 08:26:00

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