Re: plpgsql at what point does the knowledge of the query come in?

On Thu, Oct 20, 2011 at 5:42 PM, Raymond O'Donnell <rod(at)iol(dot)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.