Re: How to unnest nested arrays

út 7. 4. 2020 v 7:25 odesílatel Guyren Howe <guyren(at)gmail(dot)com> napsal:

>
>
> 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> 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.
>

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;

>