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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com