pl-pgsql, recursion and cursor contexting

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pl-pgsql, recursion and cursor contexting
Date: 2008-09-29 14:17:19
Message-ID: 0836165E8EE50F40A3DD8F0D8713726701215BB7@azsmsx421.amr.corp.intel.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message john.crawford 2008-09-29 14:21:05 database question
Previous Message Simon Riggs 2008-09-29 14:03:51 Re: pg_start_backup() takes too long