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