Postgres Pro
Downloads
Home   >   mailing lists

How to creata a recursive function for tree with loops in it - Mailing list pgsql-novice

From Tjibbe Rijpma
Subject How to creata a recursive function for tree with loops in it
Date
Msg-id 006901c4e0f4$a63d3e10$1a0313ac@TJIBBE
Whole thread Raw
List pgsql-novice
Tree view

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

 

CREATE OR REPLACE FUNCTION get_all_parents(INT) RETURNS SETOF activities_activities AS
'
DECLARE
_row activities_activities%ROWTYPE;
_id ALIAS FOR $1;

BEGIN

FOR _row IN
    SELECT * FROM activities_activities WHERE child_activity_id = _id LOOP
            RETURN NEXT _row;
            FOR _row IN
            SELECT * FROM get_all_parents(_row.parent_activity_id)

            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......
 
I make a function that gets all parents form any activity_id
 
 
 
 
 
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);

pgsql-novice by date:

Previous
From: Nigel Pegus
Date:
Subject: postgresql compile errors
Next
From: "Tjibbe Rijpma"
Date:
Subject: How to creata a recursive function for tree with loops in it