unnest on multi-dimensional arrays

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>

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.

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

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

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

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.

Cheers,

Bricklen

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

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.

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

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

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

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&gt;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
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:

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

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&gt;
<http://www.postgresql.org/__mailpref/pgsql-general
<http://www.postgresql.org/mailpref/pgsql-general&gt;&gt;

--
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&gt;

--
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 <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

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

2013/12/2 Tom Lane <tgl@sss.pgh.pa.us>

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 ...

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