From: | Belka Lambda <lambda-belka(at)yandex(dot)ru> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem: concat an array of arrays |
Date: | 2010-04-29 00:48:28 |
Message-ID: | 31681272502108@web73.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Rodriguez | 2010-04-29 01:08:48 | Performance and Clustering |
Previous Message | Jaime Casanova | 2010-04-28 22:19:48 | Re: Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints |