Thread: UNNEST and multidimensional arrays

UNNEST and multidimensional arrays

From
Mike Martin
Date:
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

Re: UNNEST and multidimensional arrays

From
Pavel Stehule
Date:
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

Re: UNNEST and multidimensional arrays

From
Steve Midgley
Date:


On Thu, Jul 30, 2020 at 8:06 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
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

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