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: pb*******@solar tec.cz
telefon: [phone]
icq: 227051816

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

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';

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Petr Bravenec <pb*******@sola rtec.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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Roberto de Amorim]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Petr Bravenec]

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: pb*******@solar tec.cz
telefon: [phone]
icq: 227051816

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster