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

From: Henry Drexler <alonup8tb(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: plpgsql at what point does the knowledge of the query come in?
Date: 2011-10-20 19:03:48
Message-ID: CAAtgU9QNoGpHS6xbqs9+A4d7_EcCHenSYReAt=H4PiCn6xcZyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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"

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Blackwell 2011-10-20 19:13:52 Cleaning up index names
Previous Message J.V. 2011-10-20 18:53:54 a set of key/value pairs