Thread: Recusrive Functions in 7.0.3
Are recursive functions allowed in in 7.0.3 as I seem to be unable to get them to work in plpgsql, it just freezes and Ihave to do ctrl-alt-c. ie calling the same function name from within some form of loop, possible to 4 or 5 levels? Also is it possible to output any debugging info, all I really want to be able to do is print out a variable to the screen. Final quesion for pure speed on functions does PL/PGSQL have special compilation options or would I get better performacefor PL/C? Regards Mark
<plpgsql@polar-digital.com> writes:
> Are recursive functions allowed in in 7.0.3
Sure.
play=> create function myfactorial(int) returns int as '
play'> begin
play'> if $1 > 1 then
play'> return $1 * myfactorial($1 - 1);
play'> end if;
play'> return $1;
play'> end;' language 'plpgsql';
CREATE
play=> select myfactorial(1);myfactorial
------------- 1
(1 row)
play=> select myfactorial(10);myfactorial
------------- 3628800
(1 row)
play=>
I get a stack overflow crash at about myfactorial(7500), but that seems
like a sufficient level of recursion depth for normal purposes ...
> as I seem to be unable to
> get them to work in plpgsql,
Are you sure you aren't asking for infinite recursion, eg by invoking
the same function with the same argument?
regards, tom lane
mark proctor <m.proctor@bigfoot.com> writes:
> However if I put the PERFORM line back in to create a recursive
> function it just goes on forever, and I only have 6 nodes.
Hm. There may be a bug here, or maybe you're still confused... but I'm
not eager to reverse-engineer your table declarations and data from this
sketch. Would you mind providing a complete example, ie a SQL script to
reproduce the problem starting from an empty database?
regards, tom lane
Ahh I found what I was doing wrong, there was a rogue value being returned causing to infinite loop. Its fixed now. Creating that script you recommended set my thinking process straight. many thanks Mark On Saturday 10 February 2001 08:42, Tom Lane wrote: > mark proctor <m.proctor@bigfoot.com> writes: > > However if I put the PERFORM line back in to create a recursive > > function it just goes on forever, and I only have 6 nodes. > > Hm. There may be a bug here, or maybe you're still confused... but I'm > not eager to reverse-engineer your table declarations and data from this > sketch. Would you mind providing a complete example, ie a SQL script to > reproduce the problem starting from an empty database? > > regards, tom lane
If I remove the line calling PERFORM it works without problems moving the children node for the passed integer into testand then exiting. I can then repeat the process going through the output to test iteratively and do not have any problems. However if I put the PERFORM line back in to create a recursive function it just goes on forever, and I only have 6 nodes. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrixWHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(rec.node_child,rec.node_parent); PERFORM get_children(rec.node_child); END LOOP; RETURN0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 03:29, Tom Lane wrote: > <plpgsql@polar-digital.com> writes: > > Are recursive functions allowed in in 7.0.3 > > Sure. > > play=> create function myfactorial(int) returns int as ' > play'> begin > play'> if $1 > 1 then > play'> return $1 * myfactorial($1 - 1); > play'> end if; > play'> return $1; > play'> end;' language 'plpgsql'; > CREATE > play=> select myfactorial(1); > myfactorial > ------------- > 1 > (1 row) > > play=> select myfactorial(10); > myfactorial > ------------- > 3628800 > (1 row) > > play=> > > I get a stack overflow crash at about myfactorial(7500), but that seems > like a sufficient level of recursion depth for normal purposes ... > > > as I seem to be unable to > > get them to work in plpgsql, > > Are you sure you aren't asking for infinite recursion, eg by invoking > the same function with the same argument? > > regards, tom lane