Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- 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$;

-- 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$;

-- unnest Nd array to 1d arrays
CREATE OR REPLACE FUNCTION unnest_nd_1d(a ANYARRAY, OUT a_1d ANYARRAY)
  RETURNS SETOF ANYARRAY
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
BEGIN
   FOREACH a_1d SLICE 1 IN ARRAY a LOOP
      RETURN NEXT;
   END LOOP;
END
$func$;

CREATE FUNCTION

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

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

unnest_nd_1d

{1,2}

{3,4}

{5,6}

SELECT 3

-- works with any dimensions
SELECT unnest_nd_1d(ARRAY[0,0])
UNION ALL
SELECT unnest_nd_1d(ARRAY[[[1,2], [3,4], [5,6]], [[7,8], [9,10], [11,12]]]);

unnest_nd_1d

{0,0}

{1,2}

{3,4}

{5,6}

{7,8}

{9,10}

{11,12}

SELECT 7

-- SQL variant works only for 2d arrays:
SELECT unnest_2d_1d(ARRAY[0,0]);

unnest_2d_1d

SELECT 0

SELECT unnest_2d_1d(ARRAY[[[1,2], [3,4], [5,6]], [[7,8], [9,10], [11,12]]]);

unnest_2d_1d

{NULL,NULL,NULL}

{NULL,NULL,NULL}

SELECT 2