> On 1 Aug 2021, at 3:30, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?Fran=C3=A7ois_Beausoleil?= <franc...@teksol.info> writes:
>> While mowing the lawn, I thought that since the syntax of multi ranges is
>> similar to arrays, maybe I could use unnest(), but sadly, that was not to be
>> the case:
>> # select
>> unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
>> ERROR: function unnest(tsmultirange) does not exist
>
> That's fixed for beta3:
>
> regression=# select
> unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
> unnest
> -----------------------------------------------
> ["2021-08-02 00:00:00","2021-08-04 00:00:00"]
> ["2021-08-07 00:00:00","2021-08-09 00:00:00")
> (2 rows)
>
>
> regards, tom lane
If what you need is behaving similar to arrays, perhaps arrays of ranges suit
your problem?
development=> select
unnest(array['[2021-08-02,2021-08-04]'::tsrange,'[2021-08-07,2021-08-09)'::tsrange]);
unnest
-----------------------------------------------
["2021-08-02 00:00:00","2021-08-04 00:00:00"]
["2021-08-07 00:00:00","2021-08-09 00:00:00")
(2 rows)
The drawback of that approach is probably with the operators and functions you
have to your avail. It seems to me though that several of those not available
for arrays could be emulated using array functions such as array_position(…)
for the contains operator, unnest with tsrange functions for others, etc.
Another approach could be to store the “rules” of the schedule and generate the
relevant portion of the multirange as a set of tsrange rows on-the-fly. That
may well perform better than storing the entire range in a table of tsrange
records.
I’ve done something like that for a hierarchical query on versioned items where
I had to base how to slice through the hierarchy on a reference timestamp. That
performed adequately on a production data warehouse, as long as you
sufficiently constrained the inputs. You can join such a function (laterally)
to some other data set too.
Regards,
Alban Hertroys
--
There is always an exception to always.
