I am struggling to understand at what point the query knowledge comes into play here.
Ideally it should look in nmarker and if there is an 'N' then execute the query (but how would it know that without running the query first?) and return the results in the nnodetest, but (in its current form it seems to be finding the first match (with the if statement commented out) then leaving the others blank.
here is the function followed by the query followed by the output:
Function
----------------------------------
--current identified issues, 1)the function seems to stop after the first match, 2)can't get it to only look at the ones with nmarker of 'N'
create or replace function nnodetest(text) returns text language plpgsql as $$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
for i in 1..(nnlength-1) loop
--if nmarker = 'N' then
select into t
node
from
(Values('one',''),('tw',''),('threee',''),('four',''),('five',''),('eights','N'),('seven',''),('eight',''),('three',''),('two','N'))
blast(node,nmarker)
where node = substring(newnode,1,i-1)||substring(newnode,i+1,nnlength);
--end if;
end loop;
return t;
END;
$$
Query
----------------------------------
select
node,
nmarker,
nnodetest(node)
from
(Values('one',''),('tw',''),('threee',''),('four',''),('five',''),('eights','N'),('seven',''),('eight',''),('three',''),('two','N'))
blast(node,nmarker)
Output
----------------------------------
"one";"";""
"tw";"";""
"threee";"";"three"
"four";"";""
"five";"";""
"eights";"N";""
"seven";"";""
"eight";"";""
"three";"";""
"two";"N";""
whereas the output should be:
"one";"";""
"tw";"";""
"threee";"";""
"four";"";""
"five";"";""
"eights";"N";"eight"
"seven";"";""
"eight";"";""
"three";"";""
"two";"N";"two"
