út 7. 4. 2020 v 4:44 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal:
> 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? >> > > The inability to reduce the number of dimensions of an existing array and > the behavior of unnest are both consequences of the implementation of > arrays in PostgreSQL. If PostgreSQL could do what your main question is > asking - using arrays only - then a function to unwrap an array one > dimension at a time would likely already exist. > > The phrase "explode any number of dimensions into 1 [dimension]" is not > accurate, unnest does away with the array entirely and gives you back its > contents, one cell per row. It is in fact removing all nesting embedded > within the array. There is no dimension because there is no longer an > array and the scalar types do not have dimensions - they are > non-dimensioned. > > The best you can probably do in SQL is take a text representation of the > data and munge it. You'll probably find array_to_string(array, delim) > useful in that regard. There may be an extension in the wild that does > this...? > > You might also investigate whether a function written in pl/perl or > pl/python gets enough information, or has better tooling available, to > handle this more gracefully. > > JSON maybe...though at that point you may want to just consider changing > the model. > is possible to write own function in PLpgSQL CREATE OR REPLACE FUNCTION unnest_nested(a anyarray, OUT r anyarray) RETURNS SETOF anyarray AS $$ BEGIN FOREACH r SLICE 1 IN ARRAY a LOOP RETURN NEXT; END LOOP; END; $$ LANGUAGE plpgsql; postgres=# select unnest_nested(array[array[1, 2], array[3, 4]]); ┌───────────────┐ │ unnest_nested │ ╞═══════════════╡ │ {1,2} │ │ {3,4} │ └───────────────┘ (2 rows) Regards Pavel > > David J. > >