Thread: plpgsql recursion

Greetings!
I have a table like:

node parent  1       2  2       3  3       4

Since i traverse this table with a recursive function, i want to avoid
infinite recursion loop. I have wrote a function to check that a new record
does not create a circular dependency. The function i wrote is as follow:

CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS ' DECLARE   traversing ALIAS FOR $1;   testing
ALIASFOR $2;   t_rec RECORD; BEGIN   FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
 
LOOP     IF t_rec.node = testing THEN       RETURN ''Circular'';     ELSE       PERFORM dba_test(t_rec.node,testing);
 END IF;   END LOOP;   RETURN ''ok'' || testing::text; END;
 
' LANGUAGE 'plpgsql';

I would use this function BEFORE inserting the new row. But if i try SELECT
dba_test(4,1); i don't have the result i expect. Can i you give me an hint
where am i wrong?

Thank you!

Stefano Vita Finzi
kluge@despammed.com

On Tue, 20 May 2003, Kluge wrote:

> Greetings!
> I have a table like:
>
> node parent
>    1       2
>    2       3
>    3       4
>
> Since i traverse this table with a recursive function, i want to avoid
> infinite recursion loop. I have wrote a function to check that a new record
> does not create a circular dependency. The function i wrote is as follow:
>
> CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
>   DECLARE
>     traversing ALIAS FOR $1;
>     testing ALIAS FOR $2;
>     t_rec RECORD;
>   BEGIN
>     FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
> LOOP
>       IF t_rec.node = testing THEN
>         RETURN ''Circular'';
>       ELSE
>         PERFORM dba_test(t_rec.node,testing);

Here you're calling dba_test recursively and ignoring its result and going
on.  I don't think that'll do what you want.  If parent is unique, I think
you can just RETURN dba_test(t_rec.node, testing).  If it's not, you'll
have to do some additional work to return the correct result.