Thread: Hierarchical queries
Hello everybody! Does someone know how to build hierarchical queries to the postgresql? I have a table with tree in it (id, parent) and need to find a way from any point of the tree to any other point. And i would like to have a list of all steps from point A to point B to make some changes on each step (this is required by the algorythm). Here is an example: treetable (where tree is stored): id parent data int4 int4 varchar(255) 0 0 root 1 0 root's chield 1 2 0 root's chield 2 3 1 root's chield 1 chield 1 4 1 root's chield 1 chield 2 5 2 root's chield 2 chield 1 6 2 root's chield 2 chield 2 And i want to get something like this: start point "root's chield 2 chield 2" finish "root's chield 1 chield 1" And the result i need: id parent data 6 2 root's chield 2 chield 2 2 0 root's chield 2 0 0 root 1 0 root's chield 1 4 1 root's chield 1 chield 2 i know that it is possible in Oracle but what about postgres? Best regards, Anton Nikiforov
There's a patch to mimic Oracle's CONNECT BY queries. You can get it at the Postgres Cookbook site: http://www.brasileiro.net/postgres/cookbook. (although it seems to be down at the moment...) On Jan 9, 2004, at 2:05 PM, Anton.Nikiforov@loteco.ru wrote: > Hello everybody! > > Does someone know how to build hierarchical queries to the postgresql? > > I have a table with tree in it (id, parent) > and need to find a way from any point of the tree to any other point. > And i would like to have a list of all steps from point A to point B > to make some changes on each step (this is required by the algorythm). > > Here is an example: > treetable (where tree is stored): > id parent data > int4 int4 varchar(255) > 0 0 root > 1 0 root's chield 1 > 2 0 root's chield 2 > 3 1 root's chield 1 chield 1 > 4 1 root's chield 1 chield 2 > 5 2 root's chield 2 chield 1 > 6 2 root's chield 2 chield 2 > > And i want to get something like this: > start point "root's chield 2 chield 2" > finish "root's chield 1 chield 1" > > And the result i need: > id parent data > 6 2 root's chield 2 chield 2 > 2 0 root's chield 2 > 0 0 root > 1 0 root's chield 1 > 4 1 root's chield 1 chield 2 > > i know that it is possible in Oracle but what about postgres? > > Best regards, > Anton Nikiforov > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
On Friday 09 January 2004 19:16, Andrew Rawnsley wrote: > There's a patch to mimic Oracle's CONNECT BY queries. You can get it > at the Postgres Cookbook site: > > http://www.brasileiro.net/postgres/cookbook. I believe I saw an announcement on freshmeat about a patch for the source to allow Oracle-style connect by. Yep: http://gppl.terminal.ru/index.eng.html I could have sworn there was something in contrib/ too, but I can't see it now. -- Richard Huxton Archonet Ltd
Look at contrib/ltree http://www.sai.msu.su/~megera/postgres/gist/ltree Oleg On Fri, 9 Jan 2004 Anton.Nikiforov@loteco.ru wrote: > Hello everybody! > > Does someone know how to build hierarchical queries to the postgresql? > > I have a table with tree in it (id, parent) > and need to find a way from any point of the tree to any other point. > And i would like to have a list of all steps from point A to point B > to make some changes on each step (this is required by the algorythm). > > Here is an example: > treetable (where tree is stored): > id parent data > int4 int4 varchar(255) > 0 0 root > 1 0 root's chield 1 > 2 0 root's chield 2 > 3 1 root's chield 1 chield 1 > 4 1 root's chield 1 chield 2 > 5 2 root's chield 2 chield 1 > 6 2 root's chield 2 chield 2 > > And i want to get something like this: > start point "root's chield 2 chield 2" > finish "root's chield 1 chield 1" > > And the result i need: > id parent data > 6 2 root's chield 2 chield 2 > 2 0 root's chield 2 > 0 0 root > 1 0 root's chield 1 > 4 1 root's chield 1 chield 2 > > i know that it is possible in Oracle but what about postgres? > > Best regards, > Anton Nikiforov > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Hello and thanks for the links, but >> http://www.brasileiro.net/postgres/cookbook. this site is still down or at least do not accsepting requests, and RH> http://gppl.terminal.ru/index.eng.html this patch is not working with my 7.4 release, i tried hier-0.3, but cannot compile my postgres with it installed. RH> I could have sworn there was something in contrib/ too, but I can't see it RH> now. Yes it is gone. :) One more URL: http://www.sai.msu.su/~megera/postgres/gist/ltree I read all but did not get how to get a tree sorted starting not from root, but from required started point of the tree getting a full path to the required finish. Best regards, Anton Nikiforov.
Hello Oleg and thanks for the link, but i could not understand how to get path from one point of the tree to another? Anyway thanks :) Best regards, Anton OB> Look at contrib/ltree OB> http://www.sai.msu.su/~megera/postgres/gist/ltree OB> Oleg OB> On Fri, 9 Jan 2004 Anton.Nikiforov@loteco.ru wrote: >> Hello everybody! >> >> Does someone know how to build hierarchical queries to the postgresql? >> >> I have a table with tree in it (id, parent) >> and need to find a way from any point of the tree to any other point. >> And i would like to have a list of all steps from point A to point B >> to make some changes on each step (this is required by the algorythm). >> >> Here is an example: >> treetable (where tree is stored): >> id parent data >> int4 int4 varchar(255) >> 0 0 root >> 1 0 root's chield 1 >> 2 0 root's chield 2 >> 3 1 root's chield 1 chield 1 >> 4 1 root's chield 1 chield 2 >> 5 2 root's chield 2 chield 1 >> 6 2 root's chield 2 chield 2 >> >> And i want to get something like this: >> start point "root's chield 2 chield 2" >> finish "root's chield 1 chield 1" >> >> And the result i need: >> id parent data >> 6 2 root's chield 2 chield 2 >> 2 0 root's chield 2 >> 0 0 root >> 1 0 root's chield 1 >> 4 1 root's chield 1 chield 2 >> >> i know that it is possible in Oracle but what about postgres? >> >> Best regards, >> Anton Nikiforov >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings >> OB> Regards, OB> Oleg OB> _____________________________________________________________ OB> Oleg Bartunov, sci.researcher, hostmaster of AstroNet, OB> Sternberg Astronomical Institute, Moscow University (Russia) OB> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ OB> phone: +007(095)939-16-83, +007(095)939-23-83 OB> ---------------------------(end of broadcast)--------------------------- OB> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Sat, 10 Jan 2004 Anton.Nikiforov@loteco.ru wrote:
> Hello Oleg and thanks for the link, but i could not understand how to
> get path from one point of the tree to another?
have you read documentation ? Get all childrens - ltree <@ ltree,
for example:
ltreetest=# select path from test where path <@ 'Top.Science';
path
------------------------------------
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(4 rows)
You should provide us example of your data and query, so we could help you.
> Anyway thanks :)
>
> Best regards,
> Anton
> OB> Look at contrib/ltree
> OB> http://www.sai.msu.su/~megera/postgres/gist/ltree
>
> OB> Oleg
> OB> On Fri, 9 Jan 2004 Anton.Nikiforov@loteco.ru wrote:
>
> >> Hello everybody!
> >>
> >> Does someone know how to build hierarchical queries to the postgresql?
> >>
> >> I have a table with tree in it (id, parent)
> >> and need to find a way from any point of the tree to any other point.
> >> And i would like to have a list of all steps from point A to point B
> >> to make some changes on each step (this is required by the algorythm).
> >>
> >> Here is an example:
> >> treetable (where tree is stored):
> >> id parent data
> >> int4 int4 varchar(255)
> >> 0 0 root
> >> 1 0 root's chield 1
> >> 2 0 root's chield 2
> >> 3 1 root's chield 1 chield 1
> >> 4 1 root's chield 1 chield 2
> >> 5 2 root's chield 2 chield 1
> >> 6 2 root's chield 2 chield 2
> >>
> >> And i want to get something like this:
> >> start point "root's chield 2 chield 2"
> >> finish "root's chield 1 chield 1"
> >>
> >> And the result i need:
> >> id parent data
> >> 6 2 root's chield 2 chield 2
> >> 2 0 root's chield 2
> >> 0 0 root
> >> 1 0 root's chield 1
> >> 4 1 root's chield 1 chield 2
> >>
> >> i know that it is possible in Oracle but what about postgres?
> >>
> >> Best regards,
> >> Anton Nikiforov
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 7: don't forget to increase your free space map settings
> >>
>
> OB> Regards,
> OB> Oleg
> OB> _____________________________________________________________
> OB> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> OB> Sternberg Astronomical Institute, Moscow University (Russia)
> OB> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> OB> phone: +007(095)939-16-83, +007(095)939-23-83
>
> OB> ---------------------------(end of broadcast)---------------------------
> OB> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Hello Oleg! There is no data yet, i'm just planning to start a new project :) Text labels are just fine and i red the documentation from the top to the very end a few times and found the way to use your module, but using it will not as beautiful as i was planning mathematicaly. You know i have (planning to have) a tree like: >> >> id parent data >> >> int4 int4 varchar(255) >> >> 0 0 root >> >> 1 0 root's chield 1 >> >> 2 0 root's chield 2 >> >> 3 1 root's chield 1 chield 1 >> >> 4 1 root's chield 1 chield 2 >> >> 5 2 root's chield 2 chield 1 >> >> 6 2 root's chield 2 chield 2 And to find a way from the record with id #6 to the record with id #3 WITH YOUR MODULE: I have to find Lowest Common Ancestor (lca) Then to find a path from id #6 to lca Then to find a path from lca to id#3 Then combine this pathes (remember that i need all steps from id #6 to id #3) And then run a special code to update all needed data (create records in different tables) IN MY BRAINS: I just need to have function that will rotate a tree and make id #6 the root element and then select a path from root (id#6) to desired id #3. As i think somebody did this already. And i'm not the first who is trying to find out the code. If i'm too stupid to understand the ability of your module - just give me a direction (i did installed your module and currently playing with it, so maybe my stupidity will become wiser and wiser in the nearest feature) :)))) Best regards, Anton OB> On Sat, 10 Jan 2004 Anton.Nikiforov@loteco.ru wrote: >> Hello Oleg and thanks for the link, but i could not understand how to >> get path from one point of the tree to another? OB> have you read documentation ? Get all childrens - ltree <@ ltree, OB> for example: OB> ltreetest=# select path from test where path <@ 'Top.Science'; OB> path OB> ------------------------------------ OB> Top.Science OB> Top.Science.Astronomy OB> Top.Science.Astronomy.Astrophysics OB> Top.Science.Astronomy.Cosmology OB> (4 rows) OB> You should provide us example of your data and query, so we could help you. >> Anyway thanks :) >> >> Best regards, >> Anton >> OB> Look at contrib/ltree >> OB> http://www.sai.msu.su/~megera/postgres/gist/ltree >> >> OB> Oleg >> OB> On Fri, 9 Jan 2004 Anton.Nikiforov@loteco.ru wrote: >> >> >> Hello everybody! >> >> >> >> Does someone know how to build hierarchical queries to the postgresql? >> >> >> >> I have a table with tree in it (id, parent) >> >> and need to find a way from any point of the tree to any other point. >> >> And i would like to have a list of all steps from point A to point B >> >> to make some changes on each step (this is required by the algorythm). >> >> >> >> Here is an example: >> >> treetable (where tree is stored): >> >> id parent data >> >> int4 int4 varchar(255) >> >> 0 0 root >> >> 1 0 root's chield 1 >> >> 2 0 root's chield 2 >> >> 3 1 root's chield 1 chield 1 >> >> 4 1 root's chield 1 chield 2 >> >> 5 2 root's chield 2 chield 1 >> >> 6 2 root's chield 2 chield 2 >> >> >> >> And i want to get something like this: >> >> start point "root's chield 2 chield 2" >> >> finish "root's chield 1 chield 1" >> >> >> >> And the result i need: >> >> id parent data >> >> 6 2 root's chield 2 chield 2 >> >> 2 0 root's chield 2 >> >> 0 0 root >> >> 1 0 root's chield 1 >> >> 4 1 root's chield 1 chield 2 >> >> >> >> i know that it is possible in Oracle but what about postgres? >> >> >> >> Best regards, >> >> Anton Nikiforov >> >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> >> TIP 7: don't forget to increase your free space map settings >> >> >> >> OB> Regards, >> OB> Oleg >> OB> _____________________________________________________________ >> OB> Oleg Bartunov, sci.researcher, hostmaster of AstroNet, >> OB> Sternberg Astronomical Institute, Moscow University (Russia) >> OB> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ >> OB> phone: +007(095)939-16-83, +007(095)939-23-83 >> >> OB> ---------------------------(end of broadcast)--------------------------- >> OB> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> OB> Regards, OB> Oleg OB> _____________________________________________________________ OB> Oleg Bartunov, sci.researcher, hostmaster of AstroNet, OB> Sternberg Astronomical Institute, Moscow University (Russia) OB> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ OB> phone: +007(095)939-16-83, +007(095)939-23-83 С уважением, IT Директор ООО "Лотэко" Антон Никифоров Тел.: +7 095 7814200 Факс: +7 095 7814201 Mail: Anton.Nikiforov@loteco.ru Web: www.loteco.ru
Anton.Nikiforov@loteco.ru wrote: > RH> I could have sworn there was something in contrib/ too, but I can't see it > RH> now. > Yes it is gone. :) See contrib/tablefunc for a function called connectby(). Joe
Hi alltogether
I have a table with two fields, d1 timestamp and dur smallint.
d1 is the starting date and dur is the duration. From this two fields
I want to generate future dates for the whole table.
There is no problem with queries where a number for the duration is
given.
test=# select d1,dur,d1 + '6 month' from t1;
d1 | dur | ?column?
-----------------------+-----+---------------------
2003-12-27 00:00:00 | 4 | 2004-06-27 00:00:00
2003-11-14 00:00:00 | 7 | 2004-05-14 00:00:00
2004-01-03 00:00:00 | 5 | 2004-07-03 00:00:00
I want to have the date plus the duration stored in the table, but
didn't succeed.
test=# select '\''||dur::varchar||' month\'' from t1;
?column?
-----------
'4 month'
'7 month'
'5 month'
but
test=# select d1 + '\''||wielange::varchar||' month\'' from t1;
ERROR: invalid input syntax for type interval: "'"
Any hints are welcome
Regards
Conni
On Sat, 10 Jan 2004, Cornelia Boenigk wrote: > I have a table with two fields, d1 timestamp and dur smallint. > d1 is the starting date and dur is the duration. From this two fields > I want to generate future dates for the whole table. I'd suggest using something like: d1 + dur * interval '1 month' rather than attempting to do it via text.
Hi Stephan Thank you > d1 + dur * interval '1 month' works ;-) Regards Conni
Thanks Joe, But this function is not giving a path from one element to other, it is just truncating the tree beginning from the start element, but it is not rotating the whole tree making starting element a tree's root. JC> See contrib/tablefunc for a function called connectby(). Regards, Anton
> -----Original Message----- > From: Anton.Nikiforov@loteco.ru [mailto:Anton.Nikiforov@loteco.ru] > Sent: Saturday, 10 January 2004 6:05 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Hierarchical queries > > > Hello everybody! > > Does someone know how to build hierarchical queries to the postgresql? > This might help you as well. http://marc.theaimsgroup.com/?l=postgresql-sql&m=106739176106877&w=2 Cheers, Graeme
Thanks Graeme! MG> http://marc.theaimsgroup.com/?l=postgresql-sql&m=106739176106877&w=2 But this function is still returning only a subtree and in addition it have a bug when calling it like SELECT * FROM crawl_tree(0,0); You will always get ERROR: out of memory But this function is clear enough to write some additional code :) regards, Anton
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
ANDparent = 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.Nikiforov@loteco.ru
Web: www.loteco.ru