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

On 7/31/21 5:16 PM, François Beausoleil wrote:
> Hello Adrian,
>

> Yes, in fact, I wrote the following:
>
> --------------------------------------------------------------------------------------------------------------------------
>
> CREATE FUNCTION generate_series(tstzrange, interval) RETURNS SETOF
> timestamp with time zone AS $$
>   SELECT n
>   FROM generate_series(lower($1), upper($1), $2) AS t0(n)
>   WHERE $1 @> n
> $$ LANGUAGE sql immutable;
>
> CREATE FUNCTION generate_series(tsrange, interval) RETURNS SETOF
> timestamp without time zone AS $$
>   SELECT n
>   FROM generate_series(lower($1), upper($1), $2) AS t0(n)
>   WHERE $1 @> n
> $$ LANGUAGE sql immutable;
>
> That was the easy part. My end goal is to iterate over a tsmultirange: I
> would like to get each individual range from a given multi range.
> Ideally, I’d like to do that without parsing the textual version of the
> multi range.
>
> 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
> LINE 1: select unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-...
>
> Apparently, PG can accept multi range values, but can’t do much with
> them at the time, except to check for inclusion/exclusion.

I see your mowing the lawn and raise walking the dog. This rang some
bells and then I remembered when in doubt consult depesz:

https://www.depesz.com/2021/07/15/how-to-get-list-of-elements-from-multiranges/

https://www.postgresql.org/message-id/20210715121508.GA30348@depesz.com

>
> Thanks for your time!
> François
>
>>> François
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com