Horton

Re: unnesting multirange data types

Jonathan S. Katz <jkatz@postgresql.org>

View thread View on postgr.es
From: "Jonathan S. Katz" <jkatz@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-06-09T20:24:27Z
Lists: pgsql-hackers 
On 6/9/21 3:44 PM, Jonathan S. Katz wrote:
> On 6/9/21 3:25 PM, Tom Lane wrote:
>> "Jonathan S. Katz" <jkatz@postgresql.org> writes:
>>> I would like to decompose the returned multirange into its individual
>>> ranges, similarly to how I would "unnest" an array:
>>
>> +1 for adding such a feature, but I suppose it's too late for v14.
> 
> Well, the case I would make for v14 is that, as of right now, the onus
> is on the driver writers / application developers to be able to unpack
> the multiranges.
> 
> I haven't tried manipulating a multirange in a PL like Python, maybe
> some exploration there would unveil more or less pain, or if it could be
> iterated over in PL/pgSQL (I'm suspecting no).

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.

In PL/Python, both range types and multirange types are treated as
strings. From there, you can at least ultimately parse and manipulate it
into your preferred Python types, but this goes back to my earlier point
about putting the onus on the developer to do so.

Thanks,

Jonathan

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