út 7. 4. 2020 v 7:25 odesílatel Guyren Howe <guy...@gmail.com> napsal:
> > > On Apr 6, 2020, at 19:44 , David G. Johnston <david.g.johns...@gmail.com> > wrote: > > On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guy...@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. > 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; >