[BUGS] FOR-IN-EXECUTE: FOR does not replanned on each entry to the FOR loop

2008-03-28 Thread Eugen.Konkov 

ID |name | parent_ID
-
1a NULL
2b   1


CREATE or REPLACE FUNCTION public.get_relation(
IN par_fields text,
IN par_table text,
IN par_id int4)
RETURNS SETOF pg_catalog.record AS
$BODY$
DECLARE v_parent_ID integer;
DECLARE v_row record;
BEGIN

v_parent_ID:= par_id;

--WHILE (v_parent_ID IS NOT NULL) LOOP
-- EXECUTE 'SELECT parent_ID, name FROM akh_build WHERE ID = ' || 
quote_literal(v_parent_ID) INTO v_row;

-- return next v_row;
-- v_parent_ID= v_row.parent_ID;
--END LOOP;

FOR v_row IN EXECUTE 'SELECT parent_ID, name FROM akh_build WHERE ID = ' || 
quote_literal(v_parent_ID) LOOP

return next v_row;
v_parent_ID= v_row.parent_ID;
END LOOP;

return;

END;
$BODY$
LANGUAGE 'plpgsql' STRICT STABLE;

select * from get_relation( '', '', 2 ) as ( a int, b varchar )

FOR and WHILE results differ
WHILE returns two rows
but FOR returns only one.
and it seems that FOR does not replanned on each entry to the FOR loop
While docs says:
38.6.4. Looping Through Query Results

This is like the previous form, except that the source query is specified as 
a string expression, which is evaluated and replanned on each entry to the 
FOR loop 



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] FOR-IN-EXECUTE: FOR does not replanned on each entry to the FOR loop

2008-03-28 Thread Tom Lane 
[EMAIL PROTECTED] writes:
 --WHILE (v_parent_ID IS NOT NULL) LOOP
 -- EXECUTE 'SELECT parent_ID, name FROM akh_build WHERE ID = ' || 
 quote_literal(v_parent_ID) INTO v_row;
 -- return next v_row;
 -- v_parent_ID= v_row.parent_ID;
 --END LOOP;

 FOR v_row IN EXECUTE 'SELECT parent_ID, name FROM akh_build WHERE ID = ' || 
 quote_literal(v_parent_ID) LOOP
  return next v_row;
  v_parent_ID= v_row.parent_ID;
 END LOOP;

 FOR and WHILE results differ

Well, sure.  They are supposed to.

 While docs says:
 38.6.4. Looping Through Query Results
 
 This is like the previous form, except that the source query is specified as 
 a string expression, which is evaluated and replanned on each entry to the 
 FOR loop 

It is evaluated when starting the loop, not each time around the body
of the loop.  I can hardly even imagine what the semantics you suggest
would be like.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] FOR-IN-EXECUTE: FOR does not replanned on each entry to the FOR loop

2008-03-28 Thread Euler Taveira de Oliveira 

[EMAIL PROTECTED] wrote:


FOR and WHILE results differ
WHILE returns two rows
but FOR returns only one.
and it seems that FOR does not replanned on each entry to the FOR loop
While docs says:
38.6.4. Looping Through Query Results

This is like the previous form, except that the source query is 
specified as a string expression, which is evaluated and replanned on 
each entry to the FOR loop


You are confusing things. The statement above talks about FOR rec IN 
EXECUTE query_string LOOP ... END LOOP. There is no WHILE.
When you're using WHILE, the EXECUTE is actually evaluated inside the 
loop and not in the loop condition (that's why you're seeing another 
iteration).



--
  Euler Taveira de Oliveira
  http://www.timbira.com/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] FOR-IN-EXECUTE: FOR does not replanned on each entry to the FOR loop

2008-03-28 Thread Eugen.Konkov 

Sorry, docs have confusing me.
I think 'each entry' means each iterations of loop. So maybe it is need to 
fix doc. to describe for-in-execute more clearly


thx
- Original Message - 
From: Euler Taveira de Oliveira [EMAIL PROTECTED]

To: [EMAIL PROTECTED]
Cc: pgsql-bugs@postgresql.org
Sent: Friday, March 28, 2008 5:43 PM
Subject: Re: [BUGS] FOR-IN-EXECUTE: FOR does not replanned on each entry to 
the FOR loop




[EMAIL PROTECTED] wrote:


FOR and WHILE results differ
WHILE returns two rows
but FOR returns only one.
and it seems that FOR does not replanned on each entry to the FOR loop
While docs says:
38.6.4. Looping Through Query Results

This is like the previous form, except that the source query is specified 
as a string expression, which is evaluated and replanned on each entry to 
the FOR loop


You are confusing things. The statement above talks about FOR rec IN 
EXECUTE query_string LOOP ... END LOOP. There is no WHILE.
When you're using WHILE, the EXECUTE is actually evaluated inside the loop 
and not in the loop condition (that's why you're seeing another 
iteration).



--
  Euler Taveira de Oliveira
  http://www.timbira.com/ 



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs