> On 1 Aug 2021, at 3:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?Fran=C3=A7ois_Beausoleil?= <francois@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
thoughthat several of those not available for arrays could be emulated using array functions such as array_position(…)
forthe 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
setof 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
hierarchyon a reference timestamp. That performed adequately on a production data warehouse, as long as you
sufficientlyconstrained 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.