Thread: HOWTO: select * from array_type

hey

I have : (A)
select (string_to_array(mycolumn, ' ')) from mytable  where id = sth;

and here receive:
{'one', 'two', 'three'}
1 row

but I want 3 rows with data:
'one'
'two'
'three'


how make second select of above (A) ?

btw: is any subtrace on psql arrays ?

Hello

you need SQL function upack

create or replace function unpack(anyarray)
returns setof anyelement as $$
select $1[i]
   from generate_series(array_lover($1,1), array_upper($1,1)) g(i)
$$ language sql strict immutable;

postgres=# select * from unpack('{one, two, three}'::text[]);
 unpack
--------
 one
 two
 three
(3 rows)

regards
Pavel Stehule

2008/9/11 alefajnie <devphyl@gmail.com>:
> hey
>
> I have : (A)
> select (string_to_array(mycolumn, ' ')) from mytable  where id = sth;
>
> and here receive:
> {'one', 'two', 'three'}
> 1 row
>
> but I want 3 rows with data:
> 'one'
> 'two'
> 'three'
>
>
> how make second select of above (A) ?
>
> btw: is any subtrace on psql arrays ?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>