Thread: Recursion working in 7.3?

As I understand it, recursion is supposed to be working in 7.3 but my first attempt is overflowing the stack and
crashingthe server. I have implemented a visitation model of a tree where subordinates share a common ancestral node
(topin the table below). In this way, changes can be made to the tree without having to change the visitation numbers
ofevery node in the table, only those who share a common top node. 

I want to write a recursive routine which returns the level of a given node. Here's the code:

create function level (INTEGER,INTEGER)
       RETURNS INTEGER AS'
   DECLARE
       post ALIAS FOR $1;
       parent ALIAS for $2;
       mygd INTEGER; --my grandfather
       mydad INTEGER;
   BEGIN
 IF parent = 0 THEN
    RETURN 1;
 ELSE
    select post,parent into mydad,mygd from threads where threads.post=parent;  --get parent's info
    RETURN level(mydad,mygd)+1; --my level is my parent's level +1
 END IF;
    END;
' LANGUAGE 'plpgsql';

Note that post is a primary key so the SELECT is guaranteed to return exactly one row.

Since every sub-tree contains a sub-root (in this case 153549) whose parent is by definition 0, the recursion should
alwaysterminate. It doesn't. Running the code on any node but the top node of the table below crashes the server. 

mpip_bb=# select post,parent,top,lv,rv from threads where top = 153549;

  post  | parent |  top   | lv | rv
--------+--------+--------+----+----
 153549 |      0 | 153549 |  1 | 12
 153555 | 153549 | 153549 |  2 |  3
 153568 | 153549 | 153549 |  4 |  5
 153685 | 153549 | 153549 |  6 | 11
 153693 | 153685 | 153549 |  7 | 10
 153846 | 153693 | 153549 |  8 |  9
(6 rows)


Did I miss something?

Thanks,
Jeff Patterson
The Melanoma Patients' Information Page
http://www.mpip.org

jeff_patterson@agilent.com writes:
> create function level (INTEGER,INTEGER)
>        RETURNS INTEGER AS'
>    DECLARE
>        post ALIAS FOR $1;
>        parent ALIAS for $2;
>        mygd INTEGER; --my grandfather
>        mydad INTEGER;
>    BEGIN
>  IF parent = 0 THEN
>     RETURN 1;
>  ELSE
>     select post,parent into mydad,mygd from threads where threads.post=parent;  --get parent's info
>     RETURN level(mydad,mygd)+1; --my level is my parent's level +1
>  END IF;
>     END;
> ' LANGUAGE 'plpgsql';


You are confusing yourself by using the names 'post' and 'parent' both
as table fields and as PLPGSQL variables.  I think the SELECT in fact
returns exactly the function's arguments here, regardless of what row
the WHERE condition chooses.  Presto, infinite recursion.

BTW, you should also have some defense against the possibility that
the passed-in parent ID is bogus, so that the SELECT finds no row ...

            regards, tom lane