[SQL] How to store directory like structures?

[Axel Straschil]

Hello!

I want to store some structure like:

CREATE TABLE node
(
nodeid  SERIAL PRIMARY KEY,
parent  INT REFERENCES node(nodeid)
ON UPDATE CASCADE ON DELETE CASCADE,
label   TEXT,
UNIQUE (parent, label),
...
data 
...
);

The label is used to map a node to a directory like strukture, so i can
have a function directory_for(nodeid) which gives me
/root_label/parent_label/parent_label/my_label (root labels have NULL as parent)

The problem is the ammount of queries when i've got deep nodes, and I
often have to query if a node is "in path" of another node.

Is there a good solution to build directory-tree like datastruktures?
I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems
to do what i want, but I've no idea (and probalbly no chance) to get
that running on my system ;-(

Thanks,
AXEL.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] How to store directory like structures?

[Axel Straschil]

Hello!

>> I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems
> what's a problem with ltree ?

I think ltree would be exactly what I need, the Problem ist that I've
got absolutly no Idea how to get that thing into a running Pg 7.4 under
Gentoo and a Pg 7.3 under Fedora? 

Is there a possibility to use ltree without using the original sources
from postgresql and keep using gentoo's portage and fedora's rpm-version
of postgres?

Thanks, AXEL.
-- 
"Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in 
http://www.informatik-forum.at/showpost.php?p=206342&postcount=10


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

Re: [SQL] How to store directory like structures?

[Axel Straschil]

Hello!

> I have no experience with those beasts, but what's wrong just untar source,
> configure and compile by hand ?

+ Testing and maybe reinstall all stuff depending on posgressql on that
server every release you want to go with ;-)

Lg,
AXEL.
-- 
"Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in 
http://www.informatik-forum.at/showpost.php?p=206342&postcount=10


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

   http://archives.postgresql.org

Re: [SQL] How to store directory like structures?

[Axel Straschil]

Hello!

>   On gentoo (at least on my box) it's installed by default in 8.0, I  
> believe it was installed by default, too, on 7.4.X

Tried with gentoo just under 7.4.x and 8.0.1:

axel=# CREATE TABLE test ( path ltree);
ERROR:  type "ltree" does not exist

Any idea?

Thanks,
AXEL.
-- 
"Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in 
http://www.informatik-forum.at/showpost.php?p=206342&postcount=10


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] How to store directory like structures?

[Axel Straschil]

Hello!

> psql yourdb < /usr/share/postgresql/contrib/ltree.sql

*STRIKE*, thanks, works perfektyl now!

Lg,
AXEL.
-- 
"Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in 
http://www.informatik-forum.at/showpost.php?p=206342&postcount=10


---(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

Re: [SQL] How to store directory like structures?

[Axel Straschil]

Hello!

> psql yourdb < /usr/share/postgresql/contrib/ltree.sql

*STRIKE* ... I'm a lucky guy now ;-)
Thanks to all!

Lg,
AXEL.
-- 
"Aber naja, ich bin eher der Forentyp." Wolfibolfi's outing in 
http://www.informatik-forum.at/showpost.php?p=206342&postcount=10


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[SQL] ORDER BY with LTREE

[Axel Straschil]

Hello!

Im working with the ltree [1] datatype and have labels that can not used
directly in ltree and a want to get a tree like strukture ordered by the 
labels.

IE, I've got a table

CREATE TABLE t
(
treeLTREE,
label   TEXT
);

and data like

treelabel
--
root.1  z
root.2  c
root.2.1a
root.2.2b
root.3  i
root.4  f
root.4.1k
root.4.2c

I need a VIEW that ordery by "by tree but by label in the same hirachie", 
so the output should be

root.2  c
root.2.1 a
root.2.2 b
root.4  f
root.4.2 c
root.4.1 k
root.3  i
root.1  z

Any idea?

Thanks, 
AXEL.
[1] http://www.sai.msu.su/~megera/postgres/gist/ltree/


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] ORDER BY with LTREE

[Axel Straschil]

Hello Michael!


Below is a simple example using the data from your original message


Thanks for the example, that realy helps me!

Lg,
AXEL.

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