Обсуждение: How to implement word wrap

Database column contains large string without line feeds.
How to split it with word wrap between words ?
I tried to implement word wrap using

create temp table test (line char(7));
insert into test select repeat('aa ',10);
select * from test;

Expected result is that table test contains multiple rows and every row
contains two words:

aa aa

Instead I got string too long exception.

How to implement word wrap in PostgreSql if  string contains words of any
size separated by spaces?

Andrus.

Andrus <kobruleht2@hot.ee> wrote:

> Database column contains large string without line feeds.
> How to split it with word wrap between words ?
> I tried to implement word wrap using
>
> create temp table test (line char(7));
> insert into test select repeat('aa ',10);
> select * from test;
>
> Expected result is that table test contains multiple rows and every row
> contains two words:
>
> aa aa
>
> Instead I got string too long exception.
>
> How to implement word wrap in PostgreSql if  string contains words of any
> size separated by spaces?

I think you have to write a function (plpgsql, plperl, ...), counting
chars per line and change space to newline if no more space in the line.
That's not really a SQL-problem ...

Maybe there are some perl-modules for that available, i don't know.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> Just realised that's not what you're after, but my first point still
> stands.

Thank you.
I tried to wrap words at 15 characters using code below.

Issues:

1. Table rows places same word to multiple lines. How to remove them so that
every word appears only in single row?
2. In last select   sum(word||' ')  causes error. How to concatenate words
bact to row (inverse of unnest() function ?

Andrus.


create temp table words( id serial, word text ) on commit drop;
insert into words (word) select * from unnest(string_to_array('Quick brown
fox runs in forest.',' '));

create temp table results on commit drop as
select
  first.id as first,
  last.id as last,
  sum(length(a.word)+1) as charcount
from words a, words first, words last
where a.id between first.id and last.id
group by 1,2
having sum(length(a.word)+1)<15;

create temp table maxr on commit drop as
select
  first,
  max(charcount) as charcount
from results
group by 1;

create temp table rows on commit drop as
select
  first,
  last
from results
join maxr using (first,charcount)
order by 1;

select
  rows.first,
  sum(word||' ')
 from rows, words
where words.id between first and last
group by 1
order by 1, words.id

On 30 Mar 2010, at 11:32, Andrus wrote:

>> Just realised that's not what you're after, but my first point still stands.
>
> Thank you.
> I tried to wrap words at 15 characters using code below.


Really, write a stored procedure that accepts (text, line_length) and returns SETOF text. You could even add
hyphenationfor the appropriate language if you go that route. For the latter it's probably best to write it in C so you
canlink hyphenation libraries to your code. 

Another approach that may be viable is to use windowing functions, but I'm not so sure it's possible to have a window
thatis being defined by the data it's running over (eg. a window defined by the length of an accumulated line of text). 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb1d23410411798520618!

> Really, write a stored procedure that accepts (text, line_length) and
> returns SETOF text. You could even add hyphenation for the appropriate
> language if you go that route. For the latter it's probably best to write
> it in C so you can link hyphenation libraries to your code.
>
> Another approach that may be viable is to use windowing functions, but I'm
> not so sure it's possible to have a window that is being defined by the
> data it's running over (eg. a window defined by the length of an
> accumulated line of text).

Implementations from http://sqlserverpedia.com/wiki/Word_Wrap_a_String
and from http://docstore.mik.ua/orelly/oracle/prog2/ch11_02.htm#AUTOID-10508
paragraph 11.2.2 did not work in Postgres.
I created method below. Is this best code for this ?

Andrus.

CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
  words text[] := string_to_array(line,' ');
  i integer;
  res text:='';

BEGIN
  if trim(line)='' then
    return next '';
    return;
    end if;
 for i IN 1 .. array_upper(words,1) LOOP
   if length(res)+length(words[i]) > linelen THEN
     return next res;
     res := '';
     END IF ;
   if res<>'' then
     res := res || ' ';
     end if;
   res := res || words[i];
   end loop;
return next res;
END
 $$ LANGUAGE plpgsql;