Re: Help with writing a generate_series(tsmultirange, interval)

> On 1 Aug 2021, at 3:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> =?utf-8?Q?Fran=C3=A7ois_Beausoleil?= <francois(at)teksol(dot)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.