Re: generic way to retrieve array as rowset

am 03.01.2006, um 13:24:54 +0100 mailte SunWuKung folgendes:
> Unfortunately the number of elements in the array is not known
> beforehand. The dimension of the array is always 1, but the number of
> elements changes from 50-500.

Okay.

test=# select * from t1;
id | foo
----+-----------------
1 | {1,2}
2 | {10,20}
3 | {100,200}
4 | {1,2,3,4,5,6,7}
(4 rows)

test=# select * from (select id, foo[idx.i] from t1, generate_series (1, (select max(array_upper(foo,1)) from t1)) idx(i))x ;
id | foo
----+-----
1 | 1
1 | 2
1 |
1 |
1 |
1 |
1 |
2 | 10
2 | 20
2 |
2 |
2 |
2 |
2 |
3 | 100
3 | 200
3 |
3 |
3 |
3 |
3 |
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
(28 rows)

test=#

test=# select * from (select id, foo[idx.i] from t1, generate_series (1, (select max(array_upper(foo,1)) from t1)) idx(i))x where foo is not NULL;
id | foo
----+-----
1 | 1
1 | 2
2 | 10
2 | 20
3 | 100
3 | 200
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
4 | 6
4 | 7
(13 rows)

test=#

ugly, i know...

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===