Postgres Pro
Downloads
Home   >   mailing lists

Re: plpgsql at what point does the knowledge of the query come in? - Mailing list pgsql-general

From Henry Drexler
Subject Re: plpgsql at what point does the knowledge of the query come in?
Date
Msg-id CAAtgU9TxOmysLZ0u__XH+_PRTtmw_aF-7thORM0AqaCw2J4nHQ@mail.gmail.com
Whole thread Raw
In response to Re: plpgsql at what point does the knowledge of the query come in?  (Henry Drexler <alonup8tb@gmail.com>)
Responses Re: plpgsql at what point does the knowledge of the query come in?  (Henry Drexler <alonup8tb@gmail.com>)
List pgsql-general
Tree view


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.

pgsql-general by date:

Previous
From: Henry Drexler
Date:
Subject: Re: plpgsql at what point does the knowledge of the query come in?
Next
From: Eduardo Morras
Date:
Subject: Re: PostGIS in a commercial project