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

On Fri, Oct 21, 2011 at 1:02 PM, Henry Drexler <alonup8tb(at)gmail(dot)com> wrote:

>
> On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell <rod(at)iol(dot)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.