On 3/4/21 4:40 PM, Tom Lane wrote:
I wonder if a 2-D integer array wouldn't be a better idea,
ie {{startpos1,length1},{startpos2,length2},...}. My experience
with working with parallel arrays in SQL has been unpleasant.
Hm, I can see your point but on the other hand I can't say my
experiences working with 2-D arrays have been that pleasant either. The
main issue being how there is no simple way to unnest just one dimension
of the array. Maybe it would be worth considering implementing a
function for that.
As far as I know to unnest just one dimension you would need to use
generate_series() or something like the query below. Please correct me
if I am wrong and there is some more ergonomic way to do it.
WITH d (a) AS (SELECT '{{2,3},{4,5}}'::int[])
SELECT array_agg(unnest) FROM d, unnest(a) WITH ORDINALITY GROUP BY
(ordinality - 1) / array_length(a, 2);
Andreas
