Обсуждение: pl-pgsql, recursion and cursor contexting
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
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> 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....
Nothing, unless you use explicitly-named cursors and force a cursor name
conflict. A for-loop's internal cursor always gets a name chosen to be
distinct from every other existing cursor, so there's no conflict.
> 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.
Wow, are they really that broken?
regards, tom lane
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 > > > >
In all fairness, I believe in Oracle I was declaring explicit cursors
(by name) and recursive calls would fail outright with complaints that
the cursor was already open. There was (to the best of my knowledge)
nothing like the "for <select...> in loop..." construct in Oracle's
PLSQL language.
-dave
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 29, 2008 10:28 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pl-pgsql, recursion and cursor contexting
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> 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....
Nothing, unless you use explicitly-named cursors and force a cursor name
conflict. A for-loop's internal cursor always gets a name chosen to be
distinct from every other existing cursor, so there's no conflict.
> 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.
Wow, are they really that broken?
regards, tom lane