Thread: Splitting text column to multiple rows

Splitting text column to multiple rows

From
"Andrus"
Date:
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;

Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
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@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@hot.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Splitting text column to multiple rows

From
"Andrus"
Date:
> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;
>
> pavel@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 ?

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;


Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/28 Andrus <kobruleht2@hot.ee>:
>> CREATE OR REPLACE FUNCTION unnest(anyarray)
>> RETURNS SETOF anyelement as $$
>>  SELECT $1[i] FROM generate_series(1,4) g(i)
>> $$ LANGUAGE sql;
>>
>> pavel@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;
>
>

Re: Splitting text column to multiple rows

From
"Andrus"
Date:
> 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.

In my sample I used joind and projecton this changes order.
How to add order number 1,2,.. to created table ?

Andrus.

Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
>> 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.
>
> In my sample I used joind and projecton this changes order.
> How to add order number 1,2,.. to created table ?
>

you cannot use join for this task

you can use some trick - using a sequences

http://www.postgresql.org/files/documentation/books/aw_pgsql/node75.html
postgres=# create temp SEQUENCE xx;
CREATE SEQUENCE
Time: 3,496 ms
postgres=# select nextval('xx'), * from gg;
 nextval | a  | b
---------+----+----
       1 | 10 | 33
       2 | 55 | 22
(2 rows)

Time: 0,926 ms
postgres=# select * from gg;
 a  | b
----+----
 10 | 33
 55 | 22
(2 rows)

regards
Pavel Stehule


> Andrus.
>

Re: Splitting text column to multiple rows

From
"Andrus"
Date:
Pavel,

> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;
>
> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
> unnest
> --------
> 23
> 2
> 3
> 4
> (4 rows)

Result is wrong: it must contain 5 rows.
How to make this work with with any array size ?

Some lines are long.
How to implement word wrap to new row in 80th position but between words
only ?

Andrus.


Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
> Pavel,
>
>> CREATE OR REPLACE FUNCTION unnest(anyarray)
>> RETURNS SETOF anyelement as $$
>>  SELECT $1[i] FROM generate_series(1,4) g(i)
>> $$ LANGUAGE sql;
>>
>> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>> unnest
>> --------
>> 23
>> 2
>> 3
>> 4
>> (4 rows)
>
> Result is wrong: it must contain 5 rows.
> How to make this work with with any array size ?

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
 SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) g(i) -- there was
error
 $$ LANGUAGE sql;

regards
Pavel
>
> Some lines are long.
> How to implement word wrap to new row in 80th position but between words
> only ?
>
> Andrus.
>

Re: Splitting text column to multiple rows

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> Pavel,
>> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>> unnest
>> --------
>> 23
>> 2
>> 3
>> 4
>> (4 rows)

> Result is wrong: it must contain 5 rows.

Surely that's a copy-and-paste mistake?  I get 5 rows from this example.

            regards, tom lane

Re: Splitting text column to multiple rows

From
"Andrus"
Date:
Pavel

thank you.
How to add word wrap to this at some column between words ?
For example string

'aaaa bbbb cccc  dddd'

if word wrap is at column 12 should produce table with two rows:

aaaa bbbb
cccc  dddd

Andrus.

Re: Splitting text column to multiple rows

From
"Andrus"
Date:
I changed unction name and tried:

CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
  SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

In this case it returns only 4 rows.
No idea what is happening.

Andrus.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrus" <kobruleht2@hot.ee>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>;
<pgsql-general@postgresql.org>
Sent: Monday, March 29, 2010 6:00 PM
Subject: Re: [GENERAL] Splitting text column to multiple rows


> "Andrus" <kobruleht2@hot.ee> writes:
>> Pavel,
>>> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>>> unnest
>>> --------
>>> 23
>>> 2
>>> 3
>>> 4
>>> (4 rows)
>
>> Result is wrong: it must contain 5 rows.
>
> Surely that's a copy-and-paste mistake?  I get 5 rows from this example.
>
> regards, tom lane


Re: Splitting text column to multiple rows

From
"Andrus"
Date:
This returns 5 rows:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
  SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest(string_to_array('23,2,3,4,5',','));

simply changing name returns 4 rows:

CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
  SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

Andrus.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrus" <kobruleht2@hot.ee>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>;
<pgsql-general@postgresql.org>
Sent: Monday, March 29, 2010 6:00 PM
Subject: Re: [GENERAL] Splitting text column to multiple rows


> "Andrus" <kobruleht2@hot.ee> writes:
>> Pavel,
>>> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>>> unnest
>>> --------
>>> 23
>>> 2
>>> 3
>>> 4
>>> (4 rows)
>
>> Result is wrong: it must contain 5 rows.
>
> Surely that's a copy-and-paste mistake?  I get 5 rows from this example.
>
> regards, tom lane


Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
> Pavel
>
> thank you.
> How to add word wrap to this at some column between words ?
> For example string
> 'aaaa bbbb cccc  dddd'
>
> if word wrap is at column 12 should produce table with two rows:
>
> aaaa bbbb
> cccc  dddd
>

You can't do it. This working only for one column.

regards
Pavel

> Andrus.
>

Re: Splitting text column to multiple rows

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> I changed unction name and tried:
> CREATE OR REPLACE FUNCTION unnest21(anyarray)
> RETURNS SETOF anyelement as $$
>   SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;

> select unnest21(string_to_array('23,2,3,4,5',','));

> In this case it returns only 4 rows.
> No idea what is happening.

Well, the generate_series call is wrong for this use ...

I think if it appeared to work before it was because the built-in
unnest() function was capturing the call.

            regards, tom lane

Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
> This returns 5 rows:
>
> CREATE OR REPLACE FUNCTION unnest(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i) <<--- 4 is constant !!!!!!!!!!!!
> $$ LANGUAGE sql;
>
> select unnest(string_to_array('23,2,3,4,5',','));
>
> simply changing name returns 4 rows:

sure .. original buggy function is here still.

Pavel

>
> CREATE OR REPLACE FUNCTION unnest21(anyarray)
> RETURNS SETOF anyelement as $$
>  SELECT $1[i] FROM generate_series(1,4) g(i)
> $$ LANGUAGE sql;
>
> select unnest21(string_to_array('23,2,3,4,5',','));
>
> Andrus.
>
> ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Andrus" <kobruleht2@hot.ee>
> Cc: "Pavel Stehule" <pavel.stehule@gmail.com>;
> <pgsql-general@postgresql.org>
> Sent: Monday, March 29, 2010 6:00 PM
> Subject: Re: [GENERAL] Splitting text column to multiple rows
>
>
>> "Andrus" <kobruleht2@hot.ee> writes:
>>>
>>> Pavel,
>>>>
>>>> pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
>>>> unnest
>>>> --------
>>>> 23
>>>> 2
>>>> 3
>>>> 4
>>>> (4 rows)
>>
>>> Result is wrong: it must contain 5 rows.
>>
>> Surely that's a copy-and-paste mistake?  I get 5 rows from this example.
>>
>> regards, tom lane
>
>

Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Tom Lane <tgl@sss.pgh.pa.us>:
> "Andrus" <kobruleht2@hot.ee> writes:
>> I changed unction name and tried:
>> CREATE OR REPLACE FUNCTION unnest21(anyarray)
>> RETURNS SETOF anyelement as $$
>>   SELECT $1[i] FROM generate_series(1,4) g(i)
>> $$ LANGUAGE sql;
>
>> select unnest21(string_to_array('23,2,3,4,5',','));
>
>> In this case it returns only 4 rows.
>> No idea what is happening.
>
> Well, the generate_series call is wrong for this use ...
>
> I think if it appeared to work before it was because the built-in
> unnest() function was capturing the call.

he uses 8.1. the bug is in "generate_series(1,>>>4<<<)"

Pavel
>
>                        regards, tom lane
>

Re: Splitting text column to multiple rows

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/3/29 Tom Lane <tgl@sss.pgh.pa.us>:
>> I think if it appeared to work before it was because the built-in
>> unnest() function was capturing the call.

> he uses 8.1. the bug is in "generate_series(1,>>>4<<<)"

If renaming the function makes it appear to work differently,
then there is another function of similar name in there somewhere.
I'm betting the server is not 8.1 after all.

            regards, tom lane

Re: ***SPAM*** Re: Splitting text column to multiple rows

From
"Andrus"
Date:
> If renaming the function makes it appear to work differently,
> then there is another function of similar name in there somewhere.
> I'm betting the server is not 8.1 after all.

I'm using

"PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit"

Andrus.

Re: ***SPAM*** Re: Splitting text column to multiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
>> If renaming the function makes it appear to work differently,
>> then there is another function of similar name in there somewhere.
>> I'm betting the server is not 8.1 after all.
>
> I'm using
>
> "PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit"
>

oh sorry, you are asked on 8.1 on yesterday

then you don't need custom unnest function.

regards
Pavel Stehule

> Andrus.
>

Re: Splitting text column tomultiple rows

From
"Andrus"
Date:
> oh sorry, you are asked on 8.1 on yesterday

I'm developing in 8.4 but customers have servers starting at 8.1
So I asked for a solution starting at 8.1
Hopefully renaming unnest to something other will work in all servers.

Andrus.

Re: Splitting text column tomultiple rows

From
Pavel Stehule
Date:
2010/3/29 Andrus <kobruleht2@hot.ee>:
>> oh sorry, you are asked on 8.1 on yesterday
>
> I'm developing in 8.4 but customers have servers starting at 8.1
> So I asked for a solution starting at 8.1 Hopefully renaming unnest to
> something other will work in all servers.
>

ok. It is better to describe your environment more.

Regards
Pavel

> Andrus.
>