Re: Recursion working in 7.3?

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