Re: unnest on multi-dimensional arrays

From: Zev Benjamin <zev-pgsql(at)strangersgate(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: unnest on multi-dimensional arrays
Date: 2013-12-02 18:38:03
Message-ID: 529CD38B.3040409@strangersgate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)strangersgate(dot)com
> <mailto:zev-pgsql(at)strangersgate(dot)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(at)strangersgate(dot)com
> <mailto:zev-pgsql(at)strangersgate(dot)com>
> <mailto:zev-pgsql(at)__strangersgate(dot)com
> <mailto:zev-pgsql(at)strangersgate(dot)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(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>
> <mailto:pgsql-general(at)__postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/__mailpref/pgsql-general
> <http://www.postgresql.org/mailpref/pgsql-general>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-12-02 18:44:43 Re: unnest on multi-dimensional arrays
Previous Message Bruce Momjian 2013-12-02 18:24:18 Re: [GENERAL] pg_upgrade ?deficiency