On Thu, Oct 20, 2011 at 5:42 PM, Raymond O'Donnell <r...@iol.ie> 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.