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]
fromgenerate_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:
CREATEORREPLACEFUNCTION public.pairwise(
cards card[]
)
RETURNStable(c1 card, c2 card)
LANGUAGEsql
AS$function$
with
individual_cards as (
select
*
from
unnest(cards) withordinality 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.
This task is not hard, but it is not supported by any special syntax. There are two forces - power of syntax, and complexity of syntax.
It can be reduced little bit
create or replace function unnest_nested2(anyarray) returns setof anyarray as $$ select array_agg(v)
from unnest($1) with ordinality v
group by (ordinality - 1) / array_length($1,1) $$ language sql;