On Thu, Oct 20, 2011 at 5:42 PM, Raymond O'Donnell <rod@xxxxxx> wrote:
I was just trying to figure your function out... :-) I think you're
mistaken about step 3 - This statement -
node = substring(newnode, 1, i-1) || substring (newnode, i+1, nnlength)
- is contatenating two substrings - the first bit (up to the i-th
character) and the rest, and then comparing that to "node".
In fact, the second substring() call looks as if it will overrun the end
of the string in "newnode".
yes is it grouping both together, see here for a visual, it is part of what I used to build it:
select
node,
substring(node,1,1-1)||substring(node,1+1,character_length(node)),
substring(node,1,2-1)||substring(node,2+1,character_length(node)),
substring(node,1,3-1)||substring(node,3+1,character_length(node)),
substring(node,1,4-1)||substring(node,4+1,character_length(node)),
substring(node,1,5-1)||substring(node,5+1,character_length(node)),
substring(node,1,6-1)||substring(node,6+1,character_length(node)),
substring(node,1,7-1)||substring(node,7+1,character_length(node))
from
(Values('threeee','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
the steps out will constrain it properly in the original function.
What version of PostgreSQL are you using? The docs for 9.0 show two
substring functions:
substring(string [from int] [for int])
substr(string, from [, count])
and a couple of variants using regexps, and what you have above doesn't
match any of them.
Ray.
Using 9.1 I have always used that syntax as it worked, though yes, looking at the docs the following also works (proper syntax):
where node = substring(newnode from 1 for i-1)||substring(newnode from i+1 for nnlength);
Thank you for the tip on the 'RAISE NOTICE statements' I have not come across that before - that should really help in my trouble shooting.
