Postgres Pro
Downloads
Home   >   mailing lists

Re: How to unnest nested arrays - Mailing list pgsql-general

From Guyren Howe
Subject Re: How to unnest nested arrays
Date
Msg-id 154C5B90-F6D0-4865-8774-85648D328BCD@gmail.com
Whole thread Raw
In response to Re: How to unnest nested arrays  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: How to unnest nested arrays
List pgsql-general
Tree view


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

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@gmail.com> 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.

ul[class*='mb-extra__public-links'], ul[class*='mb-note__public-links'], ul[class*='mb-task__public-links'] { display: none !important; }

pgsql-general by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: How to unnest nested arrays
Next
From: Pavel Stehule
Date:
Subject: Re: How to unnest nested arrays