Re: plpgsql recursion

Stefan,

I finally figured it out. After you make the final recursive call, it
returns 'circular'. However it then returns to the function that it was
called from recursively. At that point it immediately completes the loop and
the return is set to ok on up the call structure. The code should be written
as follows:

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

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Stefano Vita Finzi" <stefano(dot)vita(at)pronesis(dot)it>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, May 20, 2003 9:52 AM
Subject: [GENERAL] 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 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);
> 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(at)despammed(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>