By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,708 Members | 2,080 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,708 IT Pros & Developers. It's quick & easy.

Hierarchical queries

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a

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, An*************@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
Look at contrib/ltree
http://www.sai.msu.su/~megera/postgres/gist/ltree

Oleg
On Fri, 9 Jan 2004 An*************@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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #4

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5

P: n/a
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 An*************@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: ol**@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 ma*******@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #6

P: n/a
On Sat, 10 Jan 2004 An*************@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 An*************@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: ol**@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 ma*******@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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #7

P: n/a
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 An*************@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 An*************@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: ol**@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 ma*******@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: ol**@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: An*************@loteco.ru
Web: www.loteco.ru
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #8

P: n/a
An*************@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #9

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #10

P: n/a

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #11

P: n/a
Hi Stephan

Thank you
d1 + dur * interval '1 month'

works ;-)

Regards
Conni

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #12

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #13

P: n/a
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: An*************@loteco.ru
Web: www.loteco.ru
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.