range of composite types!

From: Jian He <hejian(dot)mark(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: range of composite types!
Date: 2022-04-26 09:16:13
Message-ID: CAMV54g0Goi140BvjvVoGK3ZHqo0hAgqr8vBBVpa0aii7RJxKhw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Lichtenberger 2022-04-26 13:55:25 pg_stat_activity.query empty
Previous Message Anthony DeBarros 2022-04-26 01:53:58 Re: Problems with installation on Mac OS

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Borodin 2022-04-26 11:22:38 Re: An attempt to avoid locally-committed-but-not-replicated-to-standby-transactions in synchronous replication
Previous Message Пантюшин Александр Иванович 2022-04-26 08:45:24 Wrong rows count in EXPLAIN