Horton

Re: unnesting multirange data types

Alexander Korotkov <aekorotkov@gmail.com>

View thread View on postgr.es
From: Alexander Korotkov <aekorotkov@gmail.com>
To: "Jonathan S. Katz" <jkatz@postgresql.org>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-06-10T17:24:20Z
Lists: pgsql-hackers

Attachments

Hi, all!

On Thu, Jun 10, 2021 at 2:00 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> On 6/9/21 4:56 PM, Alvaro Herrera wrote:
> > On 2021-Jun-09, Jonathan S. Katz wrote:
> >
> >> I did a couple more tests around this.
> >>
> >> As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
> >> multirange type.
> >
> > Uh.  This is disappointing; the need for some way to unnest or unpack a
> > multirange was mentioned multiple times in the range_agg thread.  I had
> > assumed that there was some way to cast the multirange to a range array,
> > or somehow convert it, but apparently that doesn't work.
>
> Just to be pedantic with examples:
>
>   SELECT datemultirange(
>     daterange(current_date, current_date + 2),
>     daterange(current_date + 5, current_date + 7))::daterange[];
>
>   ERROR:  cannot cast type datemultirange to daterange[]
>   LINE 1: ...2), daterange(current_date + 5, current_date + 7))::daterang...
>
> IF there was an array to cast it into an array, we could then use the
> array looping construct in PL/pgSQL, but if we could only choose one, I
> think it'd be more natural/less verbose to have an "unnest".
>
> > If the supporting pieces are mostly there, then I opine we should add
> > something.
>
> Agreed.

I agree that unnest(), cast to array and subscription are missing
points.  Proper subscription support requires expanded object
handling.  And that seems too late for v14.  But unnset() and cast to
array seems trivial.  I've drafted unnest support (attached).  I'm
going to add also cast to the array, tests, and docs within a day.
Stay tuned :)

------
Regards,
Alexander Korotkov

Commits

  1. Fix small inconsistencies in catalog definition of multirange operators

  2. Revert 29854ee8d1 due to buildfarm failures

  3. Copy-edit text for the pg_terminate_backend() "timeout" parameter.

  4. Add missing pg_description strings for pg_type entries.

  5. Fix pg_description entries for jsonb_to_record() and jsonb_to_recordset().

  6. docs: clarify JSONB operator descriptions

  7. Set procost to 10 for each of the pg_foo_is_visible() functions.

  8. Make use of LATERAL in information_schema.sequences view.

  9. Add pg_trigger_depth() function

  10. Add database comments to template0 and postgres databases, and improve