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