Thread: UNNEST and multidimensional arrays

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)

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

Hi

čt 30. 7. 2020 v 15:29 odesílatel Mike Martin <redtux1@gmail.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