Re: plpgsql recursion
От | Vincent Hikida |
---|---|
Тема | Re: plpgsql recursion |
Дата | |
Msg-id | 00fb01c3234c$d6dc8a10$6601a8c0@HOMEOFFICE обсуждение исходный текст |
Ответ на | plpgsql recursion ("Stefano Vita Finzi" <stefano.vita@pronesis.it>) |
Список | pgsql-general |
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.vita@pronesis.it> To: <pgsql-general@postgresql.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@despammed.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
В списке pgsql-general по дате отправления: