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 | CAAtgU9Q_j6iu-Qn2qDqwqauL0om_Bs4D1qdeNRmwO6O1O6918Q@mail.gmail.com Whole thread Raw |
| In response to | Re: plpgsql at what point does the knowledge of the query come in? (Raymond O'Donnell <rod@iol.ie>) |
| Responses |
Re: plpgsql at what point does the knowledge of the query
come in?
(Henry Drexler <alonup8tb@gmail.com>)
|
| List | pgsql-general |
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.
pgsql-general by date: