Re: unnest on multi-dimensional arrays

2013/12/2 Zev Benjamin <zev-pgsql(at)strangersgate(dot)com>

> Hrm. Conceptually, I think you actually want something like:
>
>
> CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
> RETURNS SETOF anyarray
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> s $1%type;
> d int;
> BEGIN
> d := array_ndims($1) - 1;
> FOREACH s SLICE d IN ARRAY $1 LOOP
>
> RETURN NEXT s;
> END LOOP;
> RETURN;
> END;
> $function$;
>
> Otherwise,
> select * from reduce_dim(ARRAY[[1], [2], [3]])
> and
> select * from reduce_dim(ARRAY[[[1], [2], [3]]);
>
> produce the same results. Unfortunately, it looks like the SLICE keyword
> only accepts a constant.
>

yes, it accept only constant - it is unpleasant, but it is necessary due
plpgsql internals :(

Regards

Pavel Stěhule

>
>
> Zev
>
>
> On 11/28/2013 02:28 AM, Pavel Stehule 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)
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2013/11/28 Zev Benjamin <zev-pgsql(at)strangersgate(dot)com
>> <mailto:zev-pgsql(at)strangersgate(dot)com>>
>>
>>
>> It appears that unnest, when called on a multi-dimensional array,
>> effectively flattens the array first. For example:
>>
>> => select * from unnest(array[array[1, 2], array[2, 3]]);
>> unnest
>> --------
>> 1
>> 2
>> 2
>> 3
>> (4 rows)
>>
>> while I would have expect something like the following:
>>
>> => select * from unnest(array[array[1, 2], array[2, 3]]);
>> unnest
>> --------
>> {1, 2}
>> {2, 3}
>> (2 rows)
>>
>> Is there any way to get the latter behavior?
>>
>>
>> Zev
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
>> <mailto:pgsql-general(at)postgresql(dot)org>)
>>
>> To make changes to your subscription:
>> http://www.postgresql.org/__mailpref/pgsql-general
>> <http://www.postgresql.org/mailpref/pgsql-general>
>>
>>
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>