Thanks, Merlin! The "restack" function solves the problem! :)
> what are you trying to do w/unfold function exactly?
The recursive query I mentioned was to produce from the argument array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11]] the result array[1,2,3,4,5,6,7,8,9,10,11].
The behaviour of the unnest function confused me, I didn't expect anything like that
----------------------------------------------------------------------
postgres=# select array(select unnest(array[array[1,2,3],array[4,5,6]]));
?column?
---------------
{1,2,3,4,5,6}
(1 row)
postgres=# select array(select unnest(array[array[1,2,3],array[4,5]]));
ERROR: multidimensional arrays must have array expressions with matching dimensions
---------------------------------------------------------------------
But, oh well, at least I can make a {...} from {{...}} in a functional way:)
Regards, Belka
29.04.10, 08:53, "Merlin Moncure" <mmoncure@xxxxxxxxx>:
> On Thu, Apr 29, 2010 at 8:46 AM, Merlin Moncure wrote:
> > On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda wrote:
> >> Hi!
> >>
> >> I tried to write a recursive SELECT, that would do the concatination, but a problem appeared:
> >> can't make a {1,2,3} from {{1,2,3}}.
> >> Here are some experiments:
> >> ---------------------------------------------------------------------------------------------------
> >> postgres=# select array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]];
> >>
> >> array
> >> --------------------------------------
> >> {{1,2,3},{4,5,6},{7,8,9},{10,11,12}}
> >> (1 row)
> >>
> >>
> >> postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
> >> )[3];
> >> array
> >> -------
> >>
> >> (1 row)
> >>
> >>
> >> postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
> >> )[3:3];
> >> array
> >> -----------
> >> {{7,8,9}}
> >> (1 row)
> >>
> >>
> >> postgres=# select (array[array[1,2,3],array[4,5,6],array[7,8,9],array[10,11,12]]
> >> )[3][1];
> >> array
> >> -------
> >> 7
> >> (1 row)
> >> -------------------------------------------------------------------------------------------
> >>
> >> The original query, that would do the concatenation:
> >> ---------------------------------------------------------------
> >> WITH RECURSIVE unfold (rest, accum) AS (
> >> VALUES ($1 :: int[][], ARRAY[] :: int[])
> >> UNION ALL
> >> SELECT u.rest[2:array_length(u.rest, 1)] AS rest, array_cat(u.rest[1], u.accum) AS accum
> >> FROM unfold AS u
> >> WHERE array_length(u.rest, 1) > 0
> >> )
> >> SELECT u.accum
> >> FROM unfold AS u
> >> WHERE array_length(u.rest, 1) = 0;
> >> ---------------------------------------------------------------
> >> Throws an error:
> >> ERROR: function array_cat(integer, integer[]) does not exist
> >
> > array_cat requires too array arguments. you could rewrite your expression to
> > array_cat(array[u.rest[1], u.accum)
> > (i think, not quite sure what you are trying to do).
> >
> > you can append scalars to arrays with the || operator:
> > select array[1,2,3] || 4;
> > ?column?
> > -----------
> > {1,2,3,4}
> >
> >
> > you can kinda sorta slice an array using the slice method:
> > select (array[array[1,2,3], array[2,4,6]])[1:1];
> > array
> > -----------
> > {{1,2,3}}
> >
> > what are you trying to do w/unfold function exactly?
>
> hm. the basic problem is that it's difficult to slide arrays up/down
> dimensions. you can move from scalars to arrays and arrays to
> scalars, but not from dimension N to N-1 etc. you can however move
> from dimension 'N' to 1:
>
> create or replace function restack(_array anyarray) returns anyarray as
> $$
> select array(select unnest($1));
> $$ language sql immutable;
>
> select restack(array[1,2,3]);
> restack
> ---------
> {1,2,3}
>
> select restack(array[array[1,2,3]]);
> restack
> ---------
> {1,2,3}
>
>
> merlin
>
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
