Обсуждение: FOR-IN-EXECUTE: FOR does not replanned on each entry to the FOR loop

ID |name | parent_ID
-------------------------
1    a             NULL
2    b               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

<Eugen.Konkov@aldec.com> 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

Eugen.Konkov@aldec.com 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/

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" <euler@timbira.com>
To: <Eugen.Konkov@aldec.com>
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


> Eugen.Konkov@aldec.com 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/