Thread: How to creata a recursive function for tree with loops in it

How to creata a recursive function for tree with loops in it

From

"Tjibbe Rijpma"

Date:

15 December 2004, 08:36:55

How to creata a (recursive) function that gets all parent object from a tree with loops in it?

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);