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.
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 |
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 |