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>:
Show quoted text
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