Thread: Problem: concat an array of arrays
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 What am I doing wrong? Please help, Belka
On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda <lambda-belka@yandex.ru> 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? merlin
On Thu, Apr 29, 2010 at 8:46 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda <lambda-belka@yandex.ru> 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
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@gmail.com>: > 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 > >