Search Postgresql Archives

Re: unnest on multi-dimensional arrays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux