Hierarchical queries

[Anton.Nikiforov]

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]

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.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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

[Oleg Bartunov]

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: [phone]

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

[Anton.Nikiforov]

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*******@postg resql.org so that your
message can get through to the mailing list cleanly

[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 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: [phone]

OB> ---------------------------(end of broadcast)---------------------------
OB> TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Oleg Bartunov]

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.Ast ronomy
Top.Science.Ast ronomy.Astrophy sics
Top.Science.Ast ronomy.Cosmolog y
(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: [phone]

OB> ---------------------------(end of broadcast)---------------------------
OB> TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.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: [phone]

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

[Anton.Nikiforov]

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.Ast ronomy
OB> Top.Science.Ast ronomy.Astrophy sics
OB> Top.Science.Ast ronomy.Cosmolog y
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: [phone]

OB> ---------------------------(end of broadcast)---------------------------
OB> TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.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: [phone]
Ñ óâàæåíèåì,
IT Äèðåêòîð ÎÎÎ "Ëîòýêî"
Àíòîí Íèêèôîðîâ
Òåë.: [phone]
Ôàêñ: [phone]
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*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Joe Conway]

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

[Cornelia Boenigk]

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::varc har||' 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