I realize I have sent a lot of messages on this thread so this will be the last one unless I come up with a solution, then I will post that.
The idea behind this is to take a string and remove one character from it successively and try to match that against any of the nodes in the query.
So for the following query 'pig dog cat' should be matched to 'pig dogcat' when 'pig dog cat' is passed through the function. The reason for this is because when successively removing characters 'pig dog cat' will get to the point of 'pig dogcat' and therefore equal to the other node. (this process can be seen in the raise notice output below).
The confusing thing is this works with other word pairs such as 'ls' 'l' and longer ones, but there are many that it fails on for some inexplicable(to me) reason.
Function:
---------------------
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('pig dogcat'),('pig dog cat')) 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;
$$
Query:
---------------------
select
node,
nnodetestt(node)
from
(Values('pig dogcat'),('pig dog cat'))
blast(node)
Raise Notice Output:
---------------------
NOTICE: number 10
NOTICE: textbreakout: ig dogcat
NOTICE: textbreakout: pg dogcat
NOTICE: textbreakout: pi dogcat
NOTICE: textbreakout: pigdogcat
NOTICE: textbreakout: pig ogcat
NOTICE: textbreakout: pig dgcat
NOTICE: textbreakout: pig docat
NOTICE: textbreakout: pig dogat
NOTICE: textbreakout: pig dogct
NOTICE: textbreakout: pig dogca
NOTICE: number 11
NOTICE: textbreakout: ig dog cat
NOTICE: textbreakout: pg dog cat
NOTICE: textbreakout: pi dog cat
NOTICE: textbreakout: pigdog cat
NOTICE: textbreakout: pig og cat
NOTICE: textbreakout: pig dg cat
NOTICE: textbreakout: pig do cat
NOTICE: textbreakout: pig dogcat <- here you can see it matches, so it should be working
NOTICE: textbreakout: pig dog at
NOTICE: textbreakout: pig dog ct
NOTICE: textbreakout: pig dog ca
Total query runtime: 12 ms.
2 rows retrieved.