Thread: Splitting text column to multiple rows
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;
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
>
> 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;
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;
>
>
> 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.
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. >
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.
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.
>
"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
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.
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
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
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. >
"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
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
>
>
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
>
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
> 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.
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. >
> 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.
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. >