Re: unnest on multi-dimensional arrays

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2013/12/2 Zev Benjamin <zev-pgsql@xxxxxxxxxxxxxxxxx>

Hrm.  Conceptually, I think you actually want something like:

CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
 RETURNS SETOF anyarray
 LANGUAGE plpgsql
AS $function$
DECLARE
  s $1%type;

  d int;
BEGIN
  d := array_ndims($1) - 1;
  FOREACH s SLICE d IN ARRAY $1 LOOP

      RETURN NEXT s;
  END LOOP;
RETURN;
END;
$function$;

Otherwise,
select * from reduce_dim(ARRAY[[1], [2], [3]])
and
select * from reduce_dim(ARRAY[[[1], [2], [3]]);

produce the same results.  Unfortunately, it looks like the SLICE keyword only accepts a constant.

yes, it accept only constant - it is unpleasant, but it is necessary due plpgsql internals :(

Regards

Pavel Stěhule

 

Zev

On 11/28/2013 02:28 AM, Pavel Stehule wrote:

Hello

postgres=# CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
  RETURNS SETOF anyarray
  LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
   FOREACH s SLICE 1  IN ARRAY $1 LOOP
       RETURN NEXT s;
   END LOOP;
RETURN;
END;
$function$;
CREATE FUNCTION

postgres=# select reduce_dim(array[array[1, 2], array[2, 3]]);
  reduce_dim
------------
  {1,2}
  {2,3}
(2 rows)

Regards

Pavel Stehule

2013/11/28 Zev Benjamin <zev-pgsql@xxxxxxxxxxxxxxxxx
<mailto:zev-pgsql@strangersgate.com>>

    It appears that unnest, when called on a multi-dimensional array,
    effectively flattens the array first.  For example:

    => select * from unnest(array[array[1, 2], array[2, 3]]);
      unnest
    --------
           1
           2
           2
           3
    (4 rows)

    while I would have expect something like the following:

    => select * from unnest(array[array[1, 2], array[2, 3]]);
      unnest
    --------
        {1, 2}
        {2, 3}
    (2 rows)

    Is there any way to get the latter behavior?


    Zev

    --
    Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx

    <mailto:pgsql-general@postgresql.org>)

    To make changes to your subscription:

    http://www.postgresql.org/__mailpref/pgsql-general
    <http://www.postgresql.org/mailpref/pgsql-general>

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general