UNNEST and multidimensional arrays

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

UNNEST and multidimensional arrays

Mike Martin
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
Reply | Threaded
Open this post in threaded view
|

Re: UNNEST and multidimensional arrays

Pavel Stehule
Hi

čt 30. 7. 2020 v 15:29 odesílatel Mike Martin <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: UNNEST and multidimensional arrays

Steve Midgley-3


On Thu, Jul 30, 2020 at 8:06 AM Pavel Stehule <[hidden email]> wrote:
Hi

čt 30. 7. 2020 v 15:29 odesílatel Mike Martin <[hidden email]> 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