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

>       IF t_rec.node = testing THEN
>         RETURN ''Circular'';
>       ELSE
>         PERFORM dba_test(t_rec.node,testing);
>       END IF;
> 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?

It's probably working as it's written :)  Once it finds 'Curcular' and
returns it to the recursed call of dba_test, you immediately throw away
the return value.

PERFORM should probably be replaced with:

ELSE -- ret is a variable SELECT dba_test(t_rec.node, testing) INTO ret;
 IF ret = ''Circular'' THEN   RETURN ret; END IF;
END IF;


More to the point, if you're attempting to prevent circular items, and
this function will be used in the form of a trigger, then simply RAISE
EXCEPTION.  This way you can ignore having to deal with returned values.


--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Can you post the recursion function that you use on this case to get out 
the information from the table ?

Thanks,
Luis Sousa

Stefano Vita Finzi 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);
>      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
>
>
>  
>