Re: Splitting text column to multiple rows

2010/3/28 Andrus <kobruleht2(at)hot(dot)ee>:
>> CREATE OR REPLACE FUNCTION unnest(anyarray)
>> RETURNS SETOF anyelement as $$
>>  SELECT $1[i] FROM generate_series(1,4) g(i)
>> $$ LANGUAGE sql;
>>
>> pavel(at)postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>
> I tried code below. Order of rows in result is different from the order of
> elements in string.
> How to preserve element order ?

no it is in same order. generate_series generates indexes from
1,2,3.... so result have to be exactly in same order. You do some
wrong.

Pavel

>
> Andrus.
>
>
> create temp table person2 ( id char(9)) on commit drop;
> insert into person2 values ('9'),('8');
> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;
>
> select * from (select unnest(string_to_array('9,23,8,7,4,5',',')) ) xx,
> person2
> order by id;
>
>