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(dot)gauthier(at)intel(dot)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
>
>
>
>