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.
I looked at the article you mention and it creates a set returning
function. I found some functions like that in the archive
- like select * from explode(array) but I don't see how to join that to
the original table the array was in.
In article <20060103120223.GC9478@webserv.wug-glas.de>,
andreas.kretschmer@schollglas.com says...
> am 03.01.2006, um 12:37:51 +0100 mailte SunWuKung folgendes:
> > When storing data in an array, like this
> >
> > id array
> > 1, {1,2}
> > 2, {10,20}
> > 3, {100,200}
>
> Forever 2 Elements in the array? Then:
>
> test=# select * from t1;
> id | foo
> ----+-----------
> 1 | {1,2}
> 2 | {10,20}
> 3 | {100,200}
> (3 rows)
>
> test=# select id, array_upper(foo,1), foo[idx.i] from t1, generate_series (1, 2) idx(i);
> id | array_upper | foo
> ----+-------------+-----
> 1 | 2 | 1
> 1 | 2 | 2
> 2 | 2 | 10
> 2 | 2 | 20
> 3 | 2 | 100
> 3 | 2 | 200
> (6 rows)
>
>
> If not: http://www.varlena.com/GeneralBits/105.php
> Read the chapter 'Querying and Flattening Array Columns'.
>
>
> HTH, Andreas
>