Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- unnest 1d arrays to elements
CREATE OR REPLACE FUNCTION unnest_1d(anyarray)
  RETURNS SETOF anyelement
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT $1[i]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) i
$func$;

-- unnest 2d array to elements
CREATE OR REPLACE FUNCTION unnest_2d(anyarray)
  RETURNS SETOF anyelement
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT $1[d1][d2]
FROM   generate_series(array_lower($1,1), array_upper($1,1)) d1
    ,  generate_series(array_lower($1,2), array_upper($1,2)) d2
$func$;

/* -- array_agg not installed by default in pg 8.3
CREATE AGGREGATE array_agg(anyelement) (
 SFUNC = array_append,
 STYPE = anyarray,
 INITCOND = '{}'
);
*/

-- unnest 2d array to 1d arrays
CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_series(array_lower($1,1), array_upper($1,1)) d1
    ,  generate_series(array_lower($1,2), array_upper($1,2)) d2
GROUP  BY d1
ORDER  BY d1
$func$;

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

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

unnest_1d

1

2

3

4

5

6

SELECT 6

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

unnest_2d

1

2

3

4

5

6

SELECT 6

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

unnest_2d_1d

{1,2}

{3,4}

{5,6}

SELECT 3