Thread: 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
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)
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>
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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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
Feel free to edit directly or suggest any changes to it.
Cheers,
Bricklen
Bricklen
2013/11/28 bricklen <bricklen@gmail.com>
+1
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 atFeel free to edit directly or suggest any changes to it.
+1
Pavel
Cheers,
Bricklen
> 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>
>
>
2013/12/2 Zev Benjamin <zev-pgsql@strangersgate.com>
Hrm. Conceptually, I think you actually want something like:d int;
CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray
LANGUAGE plpgsql
AS $function$
DECLARE
s $1%type;
BEGIN
d := array_ndims($1) - 1;
FOREACH s SLICE d IN ARRAY $1 LOOPOtherwise,
RETURN NEXT s;
END LOOP;
RETURN;
END;
$function$;
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
Pavel Stěhule
Zev
On 11/28/2013 02:28 AM, Pavel Stehule wrote:HelloRegards
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)
Pavel Stehule
2013/11/28 Zev Benjamin <zev-pgsql@strangersgate.com
<mailto:zev-pgsql@strangersgate.com>>=> select * from unnest(array[array[1, 2], array[2, 3]]);
It appears that unnest, when called on a multi-dimensional array,
effectively flattens the array first. For example:
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 <mailto:pgsql-general@postgresql.org>)http://www.postgresql.org/__mailpref/pgsql-general
To make changes to your subscription:
<http://www.postgresql.org/mailpref/pgsql-general>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
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
2013/12/2 Tom Lane <tgl@sss.pgh.pa.us>
Zev Benjamin <zev-pgsql@strangersgate.com> writes:Well, you'd also need some execution-time infrastructure to evaluate an
> This actually looks to mostly be a parser limitation:
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 ...
I afraid so it is not too simple.
There is problem with different target type when you do reduction from array to scalar and from array to array sometimes (for different slice) - so principally slice parameter should be immutable due immutable target type. Although current solution is not enough protection against same error, so then we allow a integer expression there.
Regards
Pavel
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general