Re: Splitting text column to multiple rows

Hello

try:

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',','));
unnest
--------
23
2
3
4
(4 rows)

regards
Pavel Stehule

2010/3/28 Andrus <kobruleht2(at)hot(dot)ee>:
> TEXT column contains multi-line text.
> How to split it to multiple rows so that every line is in separate row ?
> Code below should return two rows,
>
> Line 1
> Line 2
>
> Solution should work starting at 8.1
>
> Should generate_series or pgsql procedure used or any other idea?
>
> Andrus.
>
>
> create temp table test ( test text ) on commit drop;
> insert into test values( 'Line 1' ||chr(13)||'Line2');
>
> create temp table test2 ( test text ) on commit drop;
> -- todo: split test to multiple rows
> insert into test2 select * from test;
> select * from test2;
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>