> From: Pavel Stehule <pavel.stehule@gmail.com>
>To: bricklen <bricklen@gmail.com>
>Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>Sent: Thursday, 28 November 2013, 16:03
>Subject: Re: [GENERAL] unnest on multi-dimensional arrays
>
>2013/11/28 bricklen <bricklen@gmail.com>
>
>On Wed, Nov 27, 2013 at 11:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>>Hello
>>>
>>>
>>>postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
>>> RETURNS SETOF anyarray
>>> LANGUAGE plpgsql
>>>AS $function$
>>>DECLARE s $1%type;
>>>BEGIN
>>> FOREACH s SLICE 1 IN ARRAY $1 LOOP
>>> RETURN NEXT s;
>>> END LOOP;
>>>RETURN;
>>>END;
>>>$function$;
>>>CREATE FUNCTION
>>>
>>>postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
>>> reduce_dim
>>>------------
>>> {1,2}
>>> {2,3}
>>>(2 rows)
>>>
>>
>>Hi Pavel,
>>
>>
>>I hope you don't mind, I took the liberty of adding your nifty function to the Postgresql Wiki at
>>
>>https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
>>
>>
>>Feel free to edit directly or suggest any changes to it.
>>
>>
>
>+1
>
>
>Pavel
>
>
>
>>
>>Cheers,
>>
>>Bricklen
>>
>
>
In pre 9.1 I use the following:
CREATE OR REPLACE FUNCTION public.part_unnest(anyarray)
RETURNS SETOF anyarray
AS $BODY$
BEGIN
RETURN QUERY
SELECT (SELECT array_agg($1[i][i2])
FROM generate_series(array_lower($1,2), array_upper($1,2)) i2)
FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
Not sure if anyone has a cleaner / quicker example.