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

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.

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.