Thread: return next in recursive function
I have found that when I use the RETURN NEXT command in recursive function,
not all records are returned. The only records I can obtain from function
are records from the highest level of recursion. Does exist some
work-around?
Thanks
Petr Bravenec
example:
create table foo (
uid int4,
pid int4
);
insert into foo values (1,0);
insert into foo values (2,0);
insert into foo values (3,0);
insert into foo values (4,1);
insert into foo values (5,1);
insert into foo values (6,5);
insert into foo values (7,5);
insert into foo values (8,2);
create or replace function foo (integer)
returns setof foo as '
declare pid alias for $1;
declare rec RECORD;
BEGIN
FOR rec in select * from foo
where foo.pid=pid LOOP
return next rec;
raise warning ''uid=% pid=%'',rec.uid,rec.pid;
select into rec * from foo (rec.uid);
END LOOP;
return null;
end;
' language 'plpgsql';
select * from foo(0);
The output:
WARNING: uid=1 pid=0
WARNING: uid=4 pid=1
WARNING: uid=5 pid=1
WARNING: uid=6 pid=5
WARNING: uid=7 pid=5
WARNING: uid=2 pid=0
WARNING: uid=8 pid=2
WARNING: uid=3 pid=0
uid | pid
-----+-----
1 | 0
2 | 0
3 | 0
The warnings show how the output should look.
PgSQL version 7.3.4
--
email: pbravenec@solartec.cz
telefon: 777 566 384
icq: 227051816
On Thu, 2 Oct 2003, Petr Bravenec wrote: > > I have found that when I use the RETURN NEXT command in recursive function, > not all records are returned. The only records I can obtain from function > are records from the highest level of recursion. Does exist some > work-around? > create or replace function foo (integer) > returns setof foo as ' > declare pid alias for $1; > declare rec RECORD; > BEGIN > FOR rec in select * from foo > where foo.pid=pid LOOP > return next rec; > raise warning ''uid=% pid=%'',rec.uid,rec.pid; > select into rec * from foo (rec.uid); Shouldn't you be looping over these results in order to return them, something like FOR rec2 in select * from foo(rec.uid) LOOP return next rec2; END LOOP; Otherwise, why should the results from the other call become results from your call (what if you want to process them and not return all of them, or what if it's a function with a different type?) > END LOOP; > return null; > end; > ' language 'plpgsql';
Petr Bravenec <pbravenec@solartec.cz> writes:
> FOR rec in select * from foo
> where foo.pid=pid LOOP
> return next rec;
> raise warning ''uid=% pid=%'',rec.uid,rec.pid;
> select into rec * from foo (rec.uid);
> END LOOP;
> return null;
This is simply throwing away the results of the recursive call.
If you are trying to append those results to the outer call's
results, maybe do this:
FOR rec in select * from foo
where foo.pid=pid LOOP
return next rec;
raise warning ''uid=% pid=%'',rec.uid,rec.pid;
FOR rec2 in select * from foo (rec.uid) LOOP
return next rec2;
END LOOP;
END LOOP;
return null;
regards, tom lane
Hi all where can I find a case tool for PostgreeSQL? Actually I'm using the (CA) ER-Win, but it isn't integrated with PostgreSQL. TIA Roberto de Amorim
Stephan Szabo napsal(a): >>create or replace function foo (integer) >> returns setof foo as ' >> declare pid alias for $1; >> declare rec RECORD; >> BEGIN >> FOR rec in select * from foo >> where foo.pid=pid LOOP >> return next rec; >> raise warning ''uid=% pid=%'',rec.uid,rec.pid; >> select into rec * from foo (rec.uid); >> >> > >Shouldn't you be looping over these results in order to return them, >something like > FOR rec2 in select * from foo(rec.uid) LOOP > return next rec2; > END LOOP; > >Otherwise, why should the results from the other call become results from >your call (what if you want to process them and not return all of them, or >what if it's a function with a different type?) > > > Yes, I understand but I expect that the Postgres will try to do what I wrote. In the case of datatype michmach I would expect that the Postgres returns error as it do in case of select a:int4 from table1 union all select b:text from table2; How Postgres recognizes how was the function called? How can Postgres recognize the the function is called recursivelly? I can insert selected rows into temporary table and return them in the highest level of recursion when I recognized it. -- email: pbravenec@solartec.cz telefon: 777 566 384 icq: 227051816
On Fri, 3 Oct 2003, Petr Bravenec wrote: > Stephan Szabo napsal(a): > > >>create or replace function foo (integer) > >> returns setof foo as ' > >> declare pid alias for $1; > >> declare rec RECORD; > >> BEGIN > >> FOR rec in select * from foo > >> where foo.pid=pid LOOP > >> return next rec; > >> raise warning ''uid=% pid=%'',rec.uid,rec.pid; > >> select into rec * from foo (rec.uid); > >> > >> > > > >Shouldn't you be looping over these results in order to return them, > >something like > > FOR rec2 in select * from foo(rec.uid) LOOP > > return next rec2; > > END LOOP; > > > >Otherwise, why should the results from the other call become results from > >your call (what if you want to process them and not return all of them, or > >what if it's a function with a different type?) > > Yes, I understand but I expect that the Postgres will try to do what I > wrote. AFAICS it is doing exactly what you wrote. It's giving you a row in rec from the resultset of the recursive call which you do nothing with and then overwrite back at the top of the loop. It's not determining it's called recursively or anything of the sort, each function call has its own result set, there's no implicit union or any such going on. That'd be doing something other than what you wrote.