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

From: "Tjibbe Rijpma" <t(dot)b(dot)rijpma(at)student(dot)tudelft(dot)nl>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How to create a (recursive) function that get all parents from: a tree with loops in it.
Date: 2004-12-14 00:33:55
Message-ID: 03b501c4e174$9998cb40$1a0313ac@TJIBBE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message Deepa K 2004-12-14 05:41:07 Changing debug levels in postgresql
Previous Message Nigel Pegus 2004-12-13 18:43:06 postgresql compile errors