On Thu, Apr 29, 2010 at 8:46 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
> On Wed, Apr 28, 2010 at 8:48 PM, Belka Lambda <lambda-belka@xxxxxxxxx> 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
