Thread: PL/pgSQL: recursion?

Saluton,

is there any possibility to do recursion in plpgsql-functions? Here is
what I tried (I know that I can do it with a simple loop, of course; I
am just curious about what one can or cannot do in plpgsql):

,----[ test-recursion ]
| drop table ttt;
| create table ttt
|   ( id int4,
|     pid int4 );
| 
| insert into ttt values (1, NULL);
| insert into ttt values (2, NULL);
| insert into ttt values (3, 1);
| insert into ttt values (4, 1);
| insert into ttt values (5, 1);
| insert into ttt values (6, 2);
| insert into ttt values (7, 2);
| 
| drop function idPath(int4);
| create function idPath(int4) returns text as '
| declare
|   str text;
|   str2 text;
|   r record;
| begin
|   select id, pid into r from ttt where id = $1;
|   str := r.id::text;
|   if r.pid IS NOT NULL then
|     str2 := idPath(r.id);
|     str := str || '':'' || str2;
|   end if;
|   return str;
| end;' language 'plpgsql';
| 
`----

And when I tried to use it, I got:

,----
| select idPath(5);
| pqReadData() -- backend closed the channel unexpectedly.
|       This probably means the backend terminated abnormally
|       before or while processing the request.
| The connection to the server was lost. Attempting reset: Failed.
`----

I'm running Postgres 7.0.2.

Thanks in advance,

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------

"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> is there any possibility to do recursion in plpgsql-functions?

Recursion works fine ... but an infinite recursion, such as you have
here, will quickly overflow the available stack space and cause the
backend to crash.  You're invoking idPath with the same argument it
was passed, no?

I changed
> |     str2 := idPath(r.id);
to
> |     str2 := idPath(r.id-1);
and got

regression=# select idPath(5);idpath
---------5:4:3:2
(1 row)

which may or may not be the answer you wanted, but it does demonstrate
that a plpgsql function can recurse.
        regards, tom lane