[GENERAL] return next in recursive function

[Petr Bravenec]

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: [EMAIL PROTECTED]
telefon: 777 566 384
icq: 227051816




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html