Thread: How to create a (recursive) function that get all parents from: a tree with loops in it.

How to creata a (recursive) function that gets all parent objects from a
tree with loops in it? (loops: parents can be (indirect) connected with
themselves)
If I can get by the result set that's already built up by the function (with
RETURN NEXT) then with an EXCEPT it would work......, but is that possible?
See function underneath:


CREATE OR REPLACE FUNCTION get_all_parents(INT) RETURNS SETOF
activities_activities AS
'
DECLARE
_row activities_activities%ROWTYPE;


BEGIN

FOR _row IN
    SELECT * FROM activities_activities WHERE child_activity_id = $1
            LOOP
                RETURN NEXT _row;
            FOR _row IN
            SELECT * FROM get_all_parents(_row.parent_activity_id)
            --EXCEPT {SELECT * FROM (all returned _rows rows with RETURN
NEXT _row)

                LOOP
                    RETURN NEXT _row;
                END LOOP;
END LOOP;
RETURN;
END

' LANGUAGE 'plpgsql';



----------------------------------------------------------------------------
-----------------------

I have the following a tree structure:

start    10 --- cycle 11  --- drink 15  --- cycle 11   -+
             ¦             +- sail 16   -+              |
             ¦             +- dance 17  -¦
             +- walk 12   --- shoot 18  -+- climb   22 -+
             ¦             +- talk 19   -¦              ¦
             +- sleep 13  --- drink 15  -+              ¦
             +- sit 14    --- hit  20   -+              ¦
             ¦             +- help 21   -¦              ¦
             +------------------------------- read 23 --- stop 24

with a loop: cycle > drink > cycle > drink > cycle......


Build up with these SQL commands



CREATE TABLE activities
(
id INT,
name TEXT,
duration INTERVAL
) ;

CREATE TABLE activities_activities
(
parent_activity_id INT,
child_activity_id INT
);



INSERT INTO activities VALUES (10,'start', '2 hour');
INSERT INTO activities VALUES (11,'cycle', '3 hour');
INSERT INTO activities VALUES (12,'walk', '2 hour');
INSERT INTO activities VALUES (13,'sleep', '1 hour');
INSERT INTO activities VALUES (14,'sit', '4 hour');
INSERT INTO activities VALUES (15,'drink', '6 hour');
INSERT INTO activities VALUES (16,'sail', '9 hour');
INSERT INTO activities VALUES (17,'dance', '5 hour');
INSERT INTO activities VALUES (18,'shoot', '8 hour');
INSERT INTO activities VALUES (19,'talk', '3 hour');
INSERT INTO activities VALUES (20,'hit', '2 hour');
INSERT INTO activities VALUES (21,'help', '8 hour');
INSERT INTO activities VALUES (22,'climb', '6 hour');
INSERT INTO activities VALUES (23,'read', '2 hour');
INSERT INTO activities VALUES (24,'stop', '7 hour');
INSERT INTO activities_activities VALUES (10,11);
INSERT INTO activities_activities VALUES (10,12);
INSERT INTO activities_activities VALUES (10,13);
INSERT INTO activities_activities VALUES (10,14);
INSERT INTO activities_activities VALUES (11,15);
INSERT INTO activities_activities VALUES (11,16);
INSERT INTO activities_activities VALUES (11,17);
INSERT INTO activities_activities VALUES (12,18);
INSERT INTO activities_activities VALUES (12,19);
INSERT INTO activities_activities VALUES (13,15);
INSERT INTO activities_activities VALUES (14,20);
INSERT INTO activities_activities VALUES (14,21);
INSERT INTO activities_activities VALUES (15,22);
INSERT INTO activities_activities VALUES (15,11);
INSERT INTO activities_activities VALUES (16,22);
INSERT INTO activities_activities VALUES (17,22);
INSERT INTO activities_activities VALUES (18,22);
INSERT INTO activities_activities VALUES (19,22);
INSERT INTO activities_activities VALUES (20,23);
INSERT INTO activities_activities VALUES (21,23);
INSERT INTO activities_activities VALUES (22,24);
INSERT INTO activities_activities VALUES (23,24);