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@xxxxxxxxxx
Thanks for your time!
François
François
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
