Обсуждение: plpgsql at what point does the knowledge of the query come in?
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"
I found the problem, it looks like nnlength := length(newnode); is not getting reset
create or replace function nnodetestt(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
select into t
node
from
(Values('threeee','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
where node = substring(newnode,1,i-1)||substring(newnode,i+1,nnlength);
end loop;
return t;
END;
$$
select
node as node_orig,
nmarker,
nnodetestt(node),
case
when nmarker = 'N' then
nnodetestt(node)
end
from
(Values('threeee','N'),('threee',''),('fiveu','N'),('five',''))
blast(node,nmarker)
"threeee";"N";"threee";"threee"
"threee";"";"";""
"fiveu";"N";"";""
"five";"";"";""
On 20/10/2011 21:51, Henry Drexler wrote: > I found the problem, it looks like nnlength := length(newnode); is not > getting reset > > create or replace function nnodetestt(text) returns text language > plpgsql as $$ > DECLARE > newnode alias for $1; > nnlength integer; > t text; > nmarker text; > BEGIN > nnlength := length(newnode); Not sure what you mean by the above... that statement only gets executed once, so the value of nnlength doesn't change. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell <rod@iol.ie> wrote:
Not sure what you mean by the above... that statement only gets executed
once, so the value of nnlength doesn't change.
Ray.
so in the below example
threeee will match threee
but fiveu will not match five because it has not reset nnlength := length(newnode); to measure the length of fiveu but is retaining the length of threeee
is it possible to get nnlength := length(newnode); to reset for each new row/node in the query?
is it possible to get nnlength := length(newnode); to reset for each new row/node in the query?
On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell <rod@iol.ie> wrote:
Not sure what you mean by the above...
Ray.
This is what I thought it was doing.
1. it gets the node from the first row
2. measures its length
3. then loops through removing one character at a time and comparing that to the whole column/query
4. for threeee it found a match in threee
then the function starts over for the next row
1. it gets the node from the second row
2. measures the length of the string
3. then loopse ....
4. ...
so it is the step 2 that looks like is not getting reset with each new nnodetestt(node) in the select statement when it calls the function.
On 20/10/2011 22:31, Henry Drexler wrote: > > On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell <rod@iol.ie > <mailto:rod@iol.ie>> wrote: > > > > Not sure what you mean by the above... > > Ray. > > > This is what I thought it was doing. > 1. it gets the node from the first row > 2. measures its length > 3. then loops through removing one character at a time and comparing > that to the whole column/query > 4. for threeee it found a match in threee 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". 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. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 20/10/2011 22:13, Henry Drexler wrote: > On Thu, Oct 20, 2011 at 4:57 PM, Raymond O'Donnell <rod@iol.ie > <mailto:rod@iol.ie>> wrote: > > > Not sure what you mean by the above... that statement only gets executed > once, so the value of nnlength doesn't change. > > Ray. > > > > doesn't the function get executed once for each row in the query? > so in the below example Ah, OK - my misunderstanding - I thought you meant within one execution of the function. > threeee will match threee > but fiveu will not match five because it has not reset nnlength := > length(newnode); to measure the length of fiveu but is retaining the > length of threeee Are you sure about this? Try using RAISE NOTICE statements in the function to output the value of nnlength each time it's executed. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 20/10/2011 22:42, Raymond O'Donnell wrote: > - is contatenating two substrings - the first bit (up to the i-th Whoops - "concatenating" :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Thu, Oct 20, 2011 at 5:42 PM, Raymond O'Donnell <rod@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.
On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell <rod@iol.ie> wrote:
Are you sure about this? Try using RAISE NOTICE statements in the
function to output the value of nnlength each time it's executed.
Ray.
Thank you for showing me the 'Rase Notice' , I had not seen that before and it helped me solve my problem right away.
I appreciate the help you have given - thank you.
On 20/10/2011 23:16, Henry Drexler wrote: > > > On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell <rod@iol.ie > <mailto:rod@iol.ie>> wrote: > > > Are you sure about this? Try using RAISE NOTICE statements in the > function to output the value of nnlength each time it's executed. > > Ray. > > > Thank you for showing me the 'Rase Notice' , I had not seen that before > and it helped me solve my problem right away. Glad you got sorted. What was the problem in the end? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
When I was doing the mockups in excel using mid as a substitute for postgres substitute, I had to do -1 but that was actually not necessary as it was shorting the values, so the corrected plpgsql has the line
nnlength := length(newnode);
instead of nnlength := length(newnode)-1;
On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 20/10/2011 23:16, Henry Drexler wrote:
>
>
> On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell <rod@iol.ie> <mailto:rod@iol.ie>> wrote:Glad you got sorted. What was the problem in the end?
>
>
> Are you sure about this? Try using RAISE NOTICE statements in the
> function to output the value of nnlength each time it's executed.
>
> Ray.
>
>
> Thank you for showing me the 'Rase Notice' , I had not seen that before
> and it helped me solve my problem right away.
On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell <rod@iol.ie> wrote:
Glad you got sorted. What was the problem in the end?
Ray.
apart from the solution I sent earlier I have now noticed an abberation - and in testing I have not isolated but have a simple example.
for instance, using the function ln will reduce to match l but nl will not reduce to match l. There are other examples but this was the simplest I could find.
All that is going on here is removing a character from the string and comparing.
In the 'raise notice' you can see that it has properly broken up the 'nl' into first an 'n' and compared it to the next row's 'l' then it broke it into an 'l' out of the 'nl' and compared that to the 'n', bit it did not match, you will see others that have worked.
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,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'),('ls o'),('ls '),('lsn_o'),('lsn_'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i);
RAISE NOTICE 'textconv: %' , left(newnode, i-1)||right(newnode, nnlength-i);
end loop;
return t;
END;
$$
Select Statement
------------------------
select
node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'),('ls o'),('ls '),('lsn_o'),('lsn_'))
blast(node)
Output of Select Statement
------------------------
+-------+------------+
| node | nnodetestt |
+-------+------------+
| nl | |
| l | |
| ln | l |
| l | |
| ls o | ls |
| ls | |
| lsn_o | lsn_ |
| lsn_ | |
+-------+------------+
Raise Notice output (just for the first 4 rows
------------------------
NOTICE: number 2
NOTICE: number 1
CONTEXT: SQL statement "select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)"
PL/pgSQL function "nnodetestt" line 11 at SQL statement
NOTICE: textconv:
CONTEXT: SQL statement "select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)"
PL/pgSQL function "nnodetestt" line 11 at SQL statement
NOTICE: textconv: l
NOTICE: textconv: n
NOTICE: number 1
NOTICE: textconv:
NOTICE: number 2
NOTICE: textconv: n
NOTICE: number 1
CONTEXT: SQL statement "select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)"
PL/pgSQL function "nnodetestt" line 11 at SQL statement
NOTICE: textconv:
CONTEXT: SQL statement "select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)"
PL/pgSQL function "nnodetestt" line 11 at SQL statement
NOTICE: textconv: l
NOTICE: number 1
NOTICE: textconv:
Total query runtime: 19 ms.
4 rows retrieved.
On Fri, Oct 21, 2011 at 1:02 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell <rod@iol.ie> wrote:Glad you got sorted. What was the problem in the end?
Ray.apart from the solution I sent earlier I have now noticed an abberation - and in testing I have not isolated but have a simple example.for instance, using the function ln will reduce to match l but nl will not reduce to match l. There are other examples but this was the simplest I could find.All that is going on here is removing a character from the string and comparing.In the 'raise notice' you can see that it has properly broken up the 'nl' into first an 'n' and compared it to the next row's 'l' then it broke it into an 'l' out of the 'nl' and compared that to the 'n', bit it did not match, you will see others that have worked.
here is a simpler shorter example, one working, the other one not:
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('whats'),('what'),('listetomelease'),('listetomeplease')) 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;
$$
select
node,
nnodetestt(node)
from
(Values('whats'),('what'),('listetomelease'),('listetomeplease'))
blast(node)
and the messages:
NOTICE: number 5
NOTICE: nnlength 5
NOTICE: textbreakout: hats
NOTICE: nnlength 5
NOTICE: textbreakout: wats
NOTICE: nnlength 5
NOTICE: textbreakout: whts
NOTICE: nnlength 5
NOTICE: textbreakout: whas
NOTICE: nnlength 5
NOTICE: textbreakout: what
NOTICE: number 4
NOTICE: nnlength 4
NOTICE: textbreakout: hat
NOTICE: nnlength 4
NOTICE: textbreakout: wat
NOTICE: nnlength 4
NOTICE: textbreakout: wht
NOTICE: nnlength 4
NOTICE: textbreakout: wha
NOTICE: number 14
NOTICE: nnlength 14
NOTICE: textbreakout: istetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: lstetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: litetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: lisetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listtomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listeomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetmelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetoelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomlease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomelase
NOTICE: nnlength 14
NOTICE: textbreakout: listetomelese
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeleae
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeleas
NOTICE: number 15
NOTICE: nnlength 15
NOTICE: textbreakout: istetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: lstetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: litetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: lisetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listtomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listeomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetmeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetoeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomelease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomeplase
NOTICE: nnlength 15
NOTICE: textbreakout: listetomeplese
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepleae
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepleas
Total query runtime: 93 ms.
4 rows retrieved.
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.
On Fri, Oct 21, 2011 at 2:57 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
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.
Resolved.
Ray - thanks again for your help.
The pattern was it was only matching those that had a change to the end of the partner word.
so the function was going through then only returning the last comparison.
I needed to add an if statement into the function to 'return r' when it evaluated to true.
Thus:
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('one'),('on'),('fivehundredsixtyseven'),('fivehundredsixtysevens'),('one two three fou'),('one two three four'),('onetwo three ninety'),('one two three ninety')) 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);
if t = left(newnode, i-1)||right(newnode, nnlength-i) then return t;
end if;
end loop;
return t;
END;
$$
select
node,
nnodetestt(node)
from
(Values('one'),('on'),('fivehundredsixtyseven'),('fivehundredsixtysevens'),('one two three fou'),('one two three four'),('onetwo three ninety'),('one two three ninety'))
blast(node)