Thread: Array extraction

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

    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
>