Обсуждение: Array extraction
Hi!
I have such table:
user_group
---------------------------------
ug_id -> serial primary key
ug_users -> int4[]
For example table has such rows:
Vadim
I have such table:
user_group
---------------------------------
ug_id -> serial primary key
ug_users -> int4[]
For example table has such rows:
ug_id | ug_usersI want to write SQL-query or write user defined function to get such result (extract array values):
-------+--------------
1 | { 2,5,6 }
2 | { 3,4,9 }
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 >