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))xwhere 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    ===