From: | Guyren Howe <guyren(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to unnest nested arrays |
Date: | 2020-04-07 05:24:31 |
Message-ID: | 154C5B90-F6D0-4865-8774-85648D328BCD@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Apr 6, 2020, at 19:44 , David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren(at)gmail(dot)com <mailto:guyren(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2020-04-07 05:49:18 | Re: How to unnest nested arrays |
Previous Message | Pavel Stehule | 2020-04-07 05:03:57 | Re: How to unnest nested arrays |