Re: generic way to retrieve array as rowset

Hi,

Here's a modified version of A. Kretschmer's answer. This one checks
array_upper() sizes and depending on it, doesn't provide unnecessary
NULL fields. HTH.

SELECT id, val[s.i]
FROM t7
LEFT JOIN
(SELECT g.s
FROM generate_series(1,
(SELECT max(array_upper(val, 1)) FROM t7)) AS g(s)
) AS s(i)
ON (s.i <= array_upper(val, 1));

Query is inspired by the pg_database_config view in newsysview.
(Thanks AndrewSN for pointing out the source.)

Regards.

On Jan 03 12:37, SunWuKung wrote:
> When storing data in an array, like this
>
> id array
> 1, {1,2}
> 2, {10,20}
> 3, {100,200}
>
> is there a generic way to retrieve them as arowset, like this
>
> id array_dimension1
> 1 1
> 1 2
> 2 10
> 2 20
>
> By writing something like this:
>
> Select id, explode(array) From foo Where id<3