Обсуждение: unnest on multi-dimensional arrays

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

Zev Benjamin wrote
> 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?

Manually.  Cross-Join the array with a generate-series of the same length
and use the series number as an array index.  So row 1 gets cell 1 and so
forth.

Multidimensional arrays do have shortcomings in the current implementation
of which this is one.  I'm not sure, though, if there is anything
substantial and centralized in the docs so pertaining.  I may look later but
the cross-join+sequence idiom is generally useful and especially for
problems like this.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/unnest-on-multi-dimensional-arrays-tp5780706p5780709.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> Zev Benjamin wrote
>> It appears that unnest, when called on a multi-dimensional array,
>> effectively flattens the array first.  For example: ...

> Multidimensional arrays do have shortcomings in the current implementation
> of which this is one.  I'm not sure, though, if there is anything
> substantial and centralized in the docs so pertaining.

It might be worth explaining that this is a consequence of the fact that
Postgres treats all arrays over the same element type as being of the
same data type --- that is, 1-D and 2-D arrays are not distinguished
by the type system.  Thus, when the polymorphic function "unnest(anyarray)
returns setof anyelement" is applied to an integer array, it must return
a series of integers; not a series of lower-dimensional arrays.

There have been some discussions over whether this could be changed
without a backwards-compatibility disaster, but nobody sees how.

            regards, tom lane

> From: Pavel Stehule <pavel.stehule@gmail.com>

>To: bricklen <bricklen@gmail.com>
>Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
>Sent: Thursday, 28 November 2013, 16:03
>Subject: Re: [GENERAL] unnest on multi-dimensional arrays
>
>2013/11/28 bricklen <bricklen@gmail.com>
>
>On Wed, Nov 27, 2013 at 11:28 PM, Pavel Stehule <pavel.stehule@gmail.com> 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)
>>>
>>
>>Hi Pavel,
>>
>>
>>I hope you don't mind, I took the liberty of adding your nifty function to the Postgresql Wiki at
>>
>>https://wiki.postgresql.org/wiki/Unnest_multidimensional_array
>>
>>
>>Feel free to edit directly or suggest any changes to it.
>>
>>
>
>+1
>
>
>Pavel
>
> 
>
>>
>>Cheers,
>>
>>Bricklen
>>
>
>

In pre 9.1 I use the following:

CREATE OR REPLACE FUNCTION public.part_unnest(anyarray)
RETURNS SETOF anyarray
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT (SELECT array_agg($1[i][i2])
        FROM generate_series(array_lower($1,2), array_upper($1,2)) i2)
    FROM generate_series(array_lower($1,1), array_upper($1,1)) i;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

Not sure if anyone has a cleaner / quicker example.

Thanks for the explanation and examples!


Zev

On 11/28/2013 10:03 AM, Tom Lane wrote:
> David Johnston <polobo@yahoo.com> writes:
>> Zev Benjamin wrote
>>> It appears that unnest, when called on a multi-dimensional array,
>>> effectively flattens the array first.  For example: ...
>
>> Multidimensional arrays do have shortcomings in the current implementation
>> of which this is one.  I'm not sure, though, if there is anything
>> substantial and centralized in the docs so pertaining.
>
> It might be worth explaining that this is a consequence of the fact that
> Postgres treats all arrays over the same element type as being of the
> same data type --- that is, 1-D and 2-D arrays are not distinguished
> by the type system.  Thus, when the polymorphic function "unnest(anyarray)
> returns setof anyelement" is applied to an integer array, it must return
> a series of integers; not a series of lower-dimensional arrays.
>
> There have been some discussions over whether this could be changed
> without a backwards-compatibility disaster, but nobody sees how.
>
>             regards, tom lane
>
>

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.


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@strangersgate.com
> <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@postgresql.org
>     <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>
>
>

This actually looks to mostly be a parser limitation:

foreach_slice    :
                    {
                        $$ = 0;
                    }
                | K_SLICE ICONST
                    {
                        $$ = $2;
                    }
                ;

Everything after that just treats the slice number as a variable.  Is
there any underlying grammar ambiguity that prevents it from being an
expression?


Zev

On 12/02/2013 01:24 PM, Pavel Stehule wrote:
>
>
>
> 2013/12/2 Zev Benjamin <zev-pgsql@strangersgate.com
> <mailto:zev-pgsql@strangersgate.com>>
>
>     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@strangersgate.com
>         <mailto:zev-pgsql@strangersgate.com>
>         <mailto:zev-pgsql@__strangersgate.com
>         <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@postgresql.org <mailto:pgsql-general@postgresql.org>
>              <mailto:pgsql-general@__postgresql.org
>         <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>
>              <http://www.postgresql.org/__mailpref/pgsql-general
>         <http://www.postgresql.org/mailpref/pgsql-general>>
>
>
>
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <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>
>
>

Zev Benjamin <zev-pgsql@strangersgate.com> writes:
> This actually looks to mostly be a parser limitation:

Well, you'd also need some execution-time infrastructure to evaluate an
expression, if we allowed one there, but I agree it wouldn't be a
tremendously complicated patch.  We'd just not foreseen a good reason
to support an expression ...

            regards, tom lane