Re: Hierarchical queries

Hello Everybody!
Now i did what i was requesting :) One night with a computer :))
Many-many thanks to all of you :)
Below is script to create tables and function to get a path through a
tree. It is not a beautiful thing, but it is working :)
Maybe you could give me some optimization hints? :)
And maybe you could help me with the bug: when i'm calling this
function twice in a single connection i'm getting error
SELECT id, treetable.data FROM gettree(6,7) where id=treetable.id;
ERROR: relation with OID 45041919 does not exist
CONTEXT: PL/pgSQL function "gettree" line 18 at for over select rows
Do you have any idea how to deal with it?

Best regards,
Anton

treefunc-0.0.sql file follows
==============================
-- This table is made for feature caching abilities of my function. If
-- a tree big enough it will be a time consuming thing to sort it each
-- time it is needed. So i'm thinking about caching using timestamp.
DROP TABLE treeconfigtable CASCADE;
CREATE TABLE treeconfigtable (
date timestamp DEFAULT now() NOT NULL
);
INSERT INTO treeconfigtable (date) VALUES ('now');

-- This table is made only to format function's return
-- If there is a way not to use it - i'll appreciate any help
DROP TABLE pathtable CASCADE;
CREATE TABLE pathtable (
id INT4
);

-- Table that stores the tree itself
DROP SEQUENCE treesequence CASCADE;
CREATE SEQUENCE treesequence START 0 MINVALUE 0;
DROP TABLE treetable CASCADE;
CREATE TABLE treetable (
id INT4 NOT NULL PRIMARY KEY DEFAULT NEXTVAL('treesequence'),
parent INT4 NOT NULL DEFAULT 0,
data VARCHAR(255) NOT NULL,
blocked boolean DEFAULT FALSE
)
-- trigger that stores update time in treeconfigtable
DROP FUNCTION treeupdatedfunction ();
CREATE FUNCTION treeupdatedfunction () RETURNS TRIGGER AS '
BEGIN
UPDATE treeconfigtable SET date = now();
RETURN new;
END; '
LANGUAGE 'plpgsql';
CREATE TRIGGER treeupdatedtrigger AFTER INSERT OR UPDATE OR DELETE ON treetable FOR EACH ROW EXECUTE PROCEDURE treeupdatedfunction();

-- This is inserts for testing, just a simple tree
INSERT INTO treetable (parent,data) VALUES (0,'root');
INSERT INTO treetable (parent,data) VALUES (0,'Chield1');
INSERT INTO treetable (parent,data) VALUES (1,'Chield1Chield1');
INSERT INTO treetable (parent,data) VALUES (0,'Chield2');
INSERT INTO treetable (parent,data) VALUES (3,'Chield2Chield2');

INSERT INTO treetable (parent,data) VALUES (4,'Ch2Ch2Ch1');
INSERT INTO treetable (parent,data) VALUES (4,'Ch2Ch2Ch2');

INSERT INTO treetable (parent,data) VALUES (2,'Ch1Ch1Ch1');
INSERT INTO treetable (parent,data) VALUES (2,'Ch1Ch1Ch2');

-- This is a main function that takes two arguments
-- ID of element FROM
-- ID of element TO
-- and rotating tree making TO element the root element.
CREATE OR REPLACE FUNCTION gettree (INT4, INT4) RETURNS SETOF pathtable AS '
DECLARE
temp RECORD;
buf INT4 := 0;
buf_record RECORD;
temp_id INT4 := 0;
record_id INT4 := 0;
record_parent INT4 := 0;
i INT4 := 0;
path RECORD;
BEGIN
CREATE TEMPORARY TABLE temptable AS SELECT * FROM treetable;
CREATE TEMPORARY TABLE tempidtable (id INT4);

-- We should start from the destination object id;
buf = $2;

-- And first of all we should fill buffer with at least one value.
FOR temp IN SELECT * FROM temptable WHERE (id = $2 OR parent = $2) AND blocked = FALSE LOOP
IF temp.id = $2 THEN
INSERT INTO tempidtable (id) VALUES (temp.parent);
temp_id = temp.id;
temp.id = temp.parent;
temp.parent = temp_id;
record_id = temp.parent;
record_parent = temp.id;
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = record_id AND parent = record_parent AND blocked = FALSE;
ELSE
INSERT INTO tempidtable (id) VALUES (temp.id);
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = temp.id AND parent = temp.parent AND blocked = FALSE;
END IF;
END LOOP;

-- And then we should continue sorting and rotating a tree to get
-- succseeded
LOOP
FOR buf_record IN SELECT id FROM tempidtable LOOP
FOR temp IN SELECT * FROM temptable WHERE (id = buf_record.id OR parent = buf_record.id) AND blocked = FALSE LOOP
IF temp.id = buf_record.id THEN
INSERT INTO tempidtable (id) VALUES (temp.parent);
temp_id = temp.id;
temp.id = temp.parent;
temp.parent = temp_id;
record_id = temp.parent;
record_parent = temp.id;
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = record_id AND parent = record_parent AND blocked = FALSE;
ELSE
INSERT INTO tempidtable (id) VALUES (temp.id);
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = temp.id AND parent = temp.parent AND blocked = FALSE;
END IF;
END LOOP;
DELETE FROM tempidtable WHERE id=buf_record.id;
END LOOP;

-- Here we are checking if something left in the buffer
-- If nothing - just exit this loop
SELECT INTO temp * FROM tempidtable LIMIT 1;
IF NOT FOUND THEN
EXIT;
END IF;
END LOOP;
-- Now lets print the path from start to the end
SELECT INTO path * from pathtable;
buf = $1;
LOOP
path.id = buf;
RETURN NEXT path;
IF i = 0 THEN
i=1;
SELECT INTO temp * from temptable where id=buf;

ELSE
SELECT INTO temp * from temptable where id=buf AND blocked = TRUE;
END IF;
UPDATE temptable SET blocked = FALSE WHERE id = temp.id AND parent = temp.parent AND blocked = TRUE;
IF FOUND THEN

buf = temp.parent;
ELSE
EXIT;
END IF;
END LOOP;

-- How we do not need temp tables anymore
DROP TABLE tempidtable;
DROP TABLE temptable;
-- And lets finish procedure output :)
RETURN NULL;
END; '
LANGUAGE 'plpgsql';

==============================
Now select from the function like this:
SELECT id, treetable.data FROM gettree(8,5) where id=treetable.id;
And you should get a path (treetable.data added only for
visualization)
id | data
----+----------------
8 | Ch1Ch1Ch2
2 | Chield1Chield1
1 | Chield1
0 | root
3 | Chield2
4 | Chield2Chield2
5 | Ch2Ch2Ch1
(7 rows)

SELECT id, treetable.data FROM gettree(6,7) where id=treetable.id;
id | data
----+----------------
6 | Ch2Ch2Ch2
4 | Chield2Chield2
3 | Chield2
0 | root
1 | Chield1
2 | Chield1Chield1
7 | Ch1Ch1Ch1
(7 rows)

ANlr> Hello everybody!

ANlr> Does someone know how to build hierarchical queries to the postgresql?

ANlr> I have a table with tree in it (id, parent)
ANlr> and need to find a way from any point of the tree to any other point.
ANlr> And i would like to have a list of all steps from point A to point B
ANlr> to make some changes on each step (this is required by the algorythm).

ANlr> Here is an example:
ANlr> treetable (where tree is stored):
ANlr> id parent data
ANlr> int4 int4 varchar(255)
ANlr> 0 0 root
ANlr> 1 0 root's chield 1
ANlr> 2 0 root's chield 2
ANlr> 3 1 root's chield 1 chield 1
ANlr> 4 1 root's chield 1 chield 2
ANlr> 5 2 root's chield 2 chield 1
ANlr> 6 2 root's chield 2 chield 2

ANlr> And i want to get something like this:
ANlr> start point "root's chield 2 chield 2"
ANlr> finish "root's chield 1 chield 1"

ANlr> And the result i need:
ANlr> id parent data
ANlr> 6 2 root's chield 2 chield 2
ANlr> 2 0 root's chield 2
ANlr> 0 0 root
ANlr> 1 0 root's chield 1
ANlr> 4 1 root's chield 1 chield 2

ANlr> i know that it is possible in Oracle but what about postgres?

ANlr> Best regards,
ANlr> Anton Nikiforov

ANlr> ---------------------------(end of broadcast)---------------------------
ANlr> TIP 7: don't forget to increase your free space map settings

С уважением,
IT Директор ООО "Лотэко"
Антон Никифоров
Тел.: +7 095 7814200
Факс: +7 095 7814201
Mail: Anton(dot)Nikiforov(at)loteco(dot)ru
Web: www.loteco.ru