Re: Array extraction

[andyk <andyk(at)commandprompt(dot)com>]

   Hi!

   Try this one:

SELECT
   a.ug_id,
   b.ug_users[a.generate_series]
FROM
   (SELECT
       ug_id,
       generate_series(1, array_upper(ug_users, 1))
    FROM
        user_group
   ) AS a,
   user_group AS b
WHERE
   a.ug_id = b.ug_id;


veejar wrote:
> Hi!
>
> I have such table:
>
> user_group
> ---------------------------------
> ug_id -> serial primary key
> ug_users -> int4[]
>
> For example table has such rows:
>  ug_id | ug_users
>
> -------+--------------
>   1    |  { 2,5,6 }
>   2    |  { 3,4,9 }
>
>   
> I want to write SQL-query or write user defined function to get such 
> result (extract array values):
>  ug_id | ug_users
> -------+--------------
>   1    |    2
>   1    |    5
>
>   1    |    6
>   2    |    3
>   2    |    4
>   2    |    9
>
> Help please to solve this problem.
> --
> Vadim