Re: How to unnest nested arrays

[Guyren Howe <guyren@xxxxxxxxx>]

On Apr 6, 2020, at 19:44 , David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@xxxxxxxxx> wrote:

Consider this:

select (array[array[1, 2], array[3, 4]])[i:i]

from generate_subscripts(array[array[1, 2], array[3, 4]], 1) i

which produces:

{{1,2}}

{{3,4}}

I expect and want, from that source:

{1, 2}

{3, 4}

Also: could we _please_ get a version of unnest that doesn’t explode any number of dimensions into 1?

Here’s a solution in pure SQL, for reference:

CREATE OR REPLACE FUNCTION public.pairwise(

    cards card[]

)

RETURNS table(c1 card, c2 card)

LANGUAGE sql

AS $function$

with

individual_cards as (

    select

        *

    from 

        unnest(cards) with ordinality c

)

select

    c(c1.suit, c1.rank),

    c(c2.suit, c2.rank)

from 

    individual_cards c1 join

    individual_cards c2 on c1.ordinality = c2.ordinality - 1

where 

    c1.ordinality % 2 = 1

    

$function$

;

Given that Postgres often (with good cause) touts its type system, it’s a shame that this basic structured type is great in many ways, but seriously flawed in really simple ones.