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

Explain

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[0]

returns the same as

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[17]

which is NULL. I quote the docs on that matter:

By default, the lower bound index value of an array's dimensions is set to one.

0 has no special meaning here. Also, with a two-dimensional arrays, you need two indexes to get a base element. Like this:

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1][2]

Result:

2

---

The first part of your message is a bit unclear.

SELECT array_dims(ARRAY[[1,2,3], [4,5,6], [7,8,9]])

Result:

[1:3][1:3]

That's two dimensions with 3 elements (1 to 3) each (9 base elements).
If you want n-1 dimensions then this is a correct result:

SELECT ARRAY (SELECT unnest('{{1,2,3}, {4,5,6}, {7,8,9}}'::int[]))

Result:

{1,2,3,4,5,6,7,8,9}

That's one dimension. unnest() always produces one base element per row. I am not sure what result you desire exactly . Your example is just another 2-dimenstional array with a missing set of curly brackets ... ?

{1,2,3}, {4,5,6}, {7,8,9}

---

If you want a slice of the array, try this notation:

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[1:2]

Result:

{{1,2,3},{4,5,6}}

Or this:

SELECT (ARRAY[[1,2,3], [4,5,6], [7,8,9]])[2:2][1:2]

Result:

{{4,5}}

To flatten the result (get a 1D array):

• How to select 1d array from 2d array postgresql

Read more in the manual here.

Function

Later test revealed that this plpgsql function is much faster. Requires Postgres 9.1 or later:

CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
  RETURNS SETOF ANYARRAY AS
$func$
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

See:

• How to unnest a 2d array into a 1d array quickly in PostgreSQL?

This is an improved and simplified version of the function Lukas posted:

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

For Postgres versions < 8.4, array_agg() is not installed by default. Create it first:

CREATE AGGREGATE array_agg(anyelement) (
 SFUNC=array_append,
 STYPE=anyarray,
 INITCOND='{}'
);

Also, generate_subscripts() is not born, yet. Use instead:

...
FROM   generate_series(array_lower($1,1), array_upper($1,1)) d1
    ,  generate_series(array_lower($1,2), array_upper($1,2)) d2
...

Call:

SELECT unnest_2d_1d(ARRAY[[1,2], [3,4], [5,6]]);

Result

{1,2}
{3,4}
{5,6}

SQL Fiddle.