Обсуждение: 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.