Unnest multidimensional array

Library Snippets

Unnest a multidimensional array

Works with PostgreSQL

9.1+

Written in

SQL

Depends on

Nothing

Purpose: To emulate the unnest functionality for a multi-dimensional array. Courtesy of Pavel Stehule, from unnest on multi-dimensional arrays

NOTE: This will not work on an array of more than 2 dimensions. For an idea on how to handle multiple dimensions, take a look at Multidimensional Array Mapping

Function

CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray AS
$function$
DECLARE
    s $1%type;
BEGIN
    FOREACH s SLICE 1  IN ARRAY $1 LOOP
        RETURN NEXT s;
    END LOOP;
    RETURN;
END;
$function$
LANGUAGE plpgsql IMMUTABLE;

select reduce_dim(array[array[1, 2], array[2, 3], array[4,5], array[9,10]]);
 reduce_dim
------------
 {1,2}
 {2,3}
 {4,5}
 {9,10}