On Fri, Oct 21, 2011 at 1:02 PM, Henry Drexler <alonup...@gmail.com> wrote:
> > On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell <r...@iol.ie> wrote: > >> >> Glad you got sorted. What was the problem in the end? >> >> Ray. >> >> apart from the solution I sent earlier I have now noticed an abberation - > and in testing I have not isolated but have a simple example. > > for instance, using the function ln will reduce to match l but nl will not > reduce to match l. There are other examples but this was the simplest I > could find. > > All that is going on here is removing a character from the string and > comparing. > > > In the 'raise notice' you can see that it has properly broken up the 'nl' > into first an 'n' and compared it to the next row's 'l' then it broke it > into an 'l' out of the 'nl' and compared that to the 'n', bit it did not > match, you will see others that have worked. > > here is a simpler shorter example, one working, the other one not: create or replace function nnodetestt(text) returns text language plpgsql immutable as $$ DECLARE newnode alias for $1; nnlength integer; t text; nmarker text; BEGIN nnlength := length(newnode); RAISE NOTICE 'number %', nnlength; for i in 1..(nnlength) loop select into t node from (Values('whats'),('what'),('listetomelease'),('listetomeplease')) blast(node) where node = left(newnode, i-1)||right(newnode, nnlength-i); RAISE NOTICE 'nnlength %', nnlength; --raise notice 'increment %',right(newnode, nnlength-i); RAISE NOTICE 'textbreakout: %' , left(newnode, i-1)||right(newnode, nnlength-i); end loop; return t; END; $$ select node, nnodetestt(node) from (Values('whats'),('what'),('listetomelease'),('listetomeplease')) blast(node) and the messages: NOTICE: number 5 NOTICE: nnlength 5 NOTICE: textbreakout: hats NOTICE: nnlength 5 NOTICE: textbreakout: wats NOTICE: nnlength 5 NOTICE: textbreakout: whts NOTICE: nnlength 5 NOTICE: textbreakout: whas NOTICE: nnlength 5 NOTICE: textbreakout: what NOTICE: number 4 NOTICE: nnlength 4 NOTICE: textbreakout: hat NOTICE: nnlength 4 NOTICE: textbreakout: wat NOTICE: nnlength 4 NOTICE: textbreakout: wht NOTICE: nnlength 4 NOTICE: textbreakout: wha NOTICE: number 14 NOTICE: nnlength 14 NOTICE: textbreakout: istetomelease NOTICE: nnlength 14 NOTICE: textbreakout: lstetomelease NOTICE: nnlength 14 NOTICE: textbreakout: litetomelease NOTICE: nnlength 14 NOTICE: textbreakout: lisetomelease NOTICE: nnlength 14 NOTICE: textbreakout: listtomelease NOTICE: nnlength 14 NOTICE: textbreakout: listeomelease NOTICE: nnlength 14 NOTICE: textbreakout: listetmelease NOTICE: nnlength 14 NOTICE: textbreakout: listetoelease NOTICE: nnlength 14 NOTICE: textbreakout: listetomlease NOTICE: nnlength 14 NOTICE: textbreakout: listetomeease NOTICE: nnlength 14 NOTICE: textbreakout: listetomelase NOTICE: nnlength 14 NOTICE: textbreakout: listetomelese NOTICE: nnlength 14 NOTICE: textbreakout: listetomeleae NOTICE: nnlength 14 NOTICE: textbreakout: listetomeleas NOTICE: number 15 NOTICE: nnlength 15 NOTICE: textbreakout: istetomeplease NOTICE: nnlength 15 NOTICE: textbreakout: lstetomeplease NOTICE: nnlength 15 NOTICE: textbreakout: litetomeplease NOTICE: nnlength 15 NOTICE: textbreakout: lisetomeplease NOTICE: nnlength 15 NOTICE: textbreakout: listtomeplease NOTICE: nnlength 15 NOTICE: textbreakout: listeomeplease NOTICE: nnlength 15 NOTICE: textbreakout: listetmeplease NOTICE: nnlength 15 NOTICE: textbreakout: listetoeplease NOTICE: nnlength 15 NOTICE: textbreakout: listetomplease NOTICE: nnlength 15 NOTICE: textbreakout: listetomelease NOTICE: nnlength 15 NOTICE: textbreakout: listetomepease NOTICE: nnlength 15 NOTICE: textbreakout: listetomeplase NOTICE: nnlength 15 NOTICE: textbreakout: listetomeplese NOTICE: nnlength 15 NOTICE: textbreakout: listetomepleae NOTICE: nnlength 15 NOTICE: textbreakout: listetomepleas Total query runtime: 93 ms. 4 rows retrieved.