Hello. Just wondering if this is possible or not.. ---------- Forwarded message --------- From: Jian He <hejian.m...@gmail.com> Date: Tue, Apr 26, 2022 at 2:46 PM Subject: range of composite types! To: pgsql-general <pgsql-gene...@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> DB Fiddle <https://dbfiddle.uk/?rdbms=postgres_14&fiddle=cdffa53650e8df576bc82d0ae2e1beef> 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> 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.
