Re: pl-pgsql, recursion and cursor contexting

Hello

every call of plpgsql function has own result, there are not any
shared result, so you need forward result from deeper call to up.

http://www.pgsql.cz/index.php/PL/pgSQL_(en)#Recursive_call_of_SRF_functions

regards
Pavel Stehule

2008/9/29 Gauthier, Dave <dave.gauthier@intel.com>:
> Hi:
>
>
>
> I'm in the business of writting recursive PL-Pgsql functions.  I need to
> know what happens to the data stream from a select cursor inside of which
> the recursive call is made.  For example....
>
>
>
>
>
>
>
> create table int_stream (num integer);
>
> insert into int_stream (num) values (1);
>
> insert into int_stream (num) values (2);
>
> insert into int_stream (num) values (3);
>
> insert into int_stream (num) values (4);
>
> insert into int_stream (num) values (5);
>
>
>
> create or replace function my_factorial(integer) returns insteger as $$
>
>
>
>   in_int alias for $1;
>
>   x integer;
>
>   rec record;
>
>
>
> begin
>
>
>
>   if(in_int = 1) then
>
>     return(1);
>
>   end if;
>
>
>
>   for rec in    select num from int_stream where num <= in_int
>
>   loop
>
>     x := in_int * my_factorial(in_int - 1);
>
>   end loop;
>
>
>
>   return(x);
>
> end;
>
> $$ language plpgsql;
>
>
>
> This comes up witht he right answer.  IOW, making the recursive call from
> within the "for rec in..." loop doesn't seem to destroy the data streams
> from earlier calls.  I just need to make sure that this will always be the
> case and that getting the correct result in this example is not just an
> artifact of it's simplicity.  I know, for example, this was a no-no in
> Oracle.  You had to stuff arrays with the resuts from looping in cursors,
> and then make the recursive call in a subsaquent loop on the arrays.
>
>
>
> Thanks
>
> -dave
>
>
>
>