Unnest array by one level

Question

I want to take an array of n dimensions and return set containing rows of arrays of n-1 dimensions. For example, take the array ARRAY[[1,2,3], [4,5,6], [7,8,9]] and return a set {1,2,3}, {4,5,6}, {7,8,9}. Using unnest returns the set 1,2,3,4,5,6,7,8,9.

I tried grabbing the unnest function from PostgreSQL 8.4, which seems like it would do what I'm looking for:

CREATE OR REPLACE FUNCTION tstng.unnest2(anyarray)
    RETURNS SETOF anyelement
    LANGUAGE plpgsql
    IMMUTABLE
    AS $$
    BEGIN
            RETURN QUERY SELECT $1[i]
                FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
        END;
    $$;

However, SELECT tstng.unnest2(ARRAY[[1,2,3], [4,5,6], [7,8,9]]); returns the set , , (i.e.: 3 null rows).

I've also found that SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0]; returns null, which I believe to be the root of my problem.

Answer 1

Slices of a multi-dimensional are returned as multi-dimensional arrays. This is a modified version of unnest that will take a 2-dimensional array and return a set of 1-dimensional arrays.

update: modified to use the built-in array_agg aggregate function that was default as of 8.4. (http://www.postgresql.org/docs/9.2/static/functions-aggregate.html)

Caveats:

• It only works for 2-dimensional arrays (I should probably rename the function to reflect that limitation).
• If you are on 8.3 (and can't upgrade), you need to have the array_accum aggregate defined and change all references in the functions below from array_agg to array_accum. http://www.postgresql.org/docs/8.3/static/xaggr.html

code:

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray)
RETURNS SETOF anyarray AS
$BODY$
  SELECT array_agg($1[series2.i][series2.x]) FROM
    (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
     FROM 
     (SELECT generate_series(array_lower($1,1),array_upper($1,1)) as i) series1 
    ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

Result:

select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]]);
unnest_multidim
----------------------
{1,2,3}
{4,5,6}
{7,8,9}
(3 rows)

Now, let's say for some reason you want easy access to just one of these arrays that is returned. The following function adds an optional index parameter that will return the nested array of the index you provide, or, if you provide null, will output the full set of "unnested" arrays.

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray, integer)
  RETURNS SETOF anyarray AS
$BODY$
SELECT array_agg($1[series2.i][series2.x]) FROM
  (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
  FROM 
    (SELECT CASE WHEN $2  IS NULL THEN
      generate_series(array_lower($1,1),array_upper($1,1)) 
      ELSE $2
      END as i) series1
  ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

Results:

db=> select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]],2);
 unnest_multidim 
-----------------
 {4,5,6}
(1 row)

db=> select unnest_multidim(array[[1,2,3],[4,5,6],[7,8,9]],NULL);
 unnest_multidim 
-----------------
 {1,2,3}
 {4,5,6}
 {7,8,9}
(3 rows)

Answer 2

A word of caution: when using array_agg on postgres <9 order may change PostgreSQL array_agg order If you plan to use the unnested array say for finding argmax, this will corrupt your data.