út 7. 4. 2020 v 4:44 odesílatel David G. Johnston <david.g.johnston@xxxxxxxxx> napsal:
On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe <guyren@xxxxxxxxx> wrote:Consider this:select (array[array[1, 2], array[3, 4]])[i:i]from generate_subscripts(array[array[1, 2], array[3, 4]], 1) iwhich 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;
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)
┌───────────────┐
│ unnest_nested │
╞═══════════════╡
│ {1,2} │
│ {3,4} │
└───────────────┘
(2 rows)
Regards
Pavel
David J.
