Re: UNNEST and multidimensional arrays

On Thu, Jul 30, 2020 at 8:06 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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
>>
>
Couldn't you write something in PLPerl (or really any procedural language)
to do what Perl does with Arrays in Postgres?
https://www.postgresql.org/docs/9.1/plperl.html

Steve