Re: UNNEST and multidimensional arrays

Hi

čt 30. 7. 2020 v 15:29 odesílatel Mike Martin <redtux1(at)gmail(dot)com> napsal:

> Hi
> Is there anyway to control how many levels unnest unnests?
> ie: if I have (in perl) an array of arrays for unnest to only unnest the
> top level, leaving the sub levels as an array, so a select would show
> {val1,val2}
> {val1,val2}
> {val1,val2}
> rather than
> val1
> val2
> val1
> val2
> val1
> val2
>
> Currently I am using a variation of the fooling code from postgres wiki
> (from 2013)
>

there is not any other way

Regards

Pavel

>
> https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
>
> CREATE OR REPLACE FUNCTION public.reduce_dim(
> anyarray)
> RETURNS SETOF anyarray
> LANGUAGE 'plpgsql'
>
> COST 100
> VOLATILE
> ROWS 1000
> AS $BODY$
> DECLARE s $1%type;
> BEGIN
> IF cardinality ($1::text[]) >0 THEN
> FOREACH s SLICE 1 IN ARRAY $1 LOOP
> RETURN NEXT s;
> END LOOP;
> END IF;
> RETURN;
> END;
> $BODY$;
>
> thanks in advance
>
> Mike
>