pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to store directory like structures?
12+ messages / 3 participants
[nested] [flat]
* How to store directory like structures?
@ 2005-04-03 10:13 Axel Straschil <axel@straschil.com>
0 siblings, 2 replies; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 10:13 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 17:00 Oleg Bartunov <oleg@sai.msu.su>
parent: Axel Straschil <axel@straschil.com>
1 sibling, 2 replies; 12+ messages in thread
From: Oleg Bartunov @ 2005-04-03 17:00 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; +Cc: pgsql-sql
On Sun, 3 Apr 2005, Axel Straschil wrote:
> 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 ;-(
what's a problem with ltree ?
>
> Thanks,
> AXEL.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 17:26 Axel Straschil <axel@straschil.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 17:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 18:09 PFC <lists@boutiquenumerique.com>
parent: Axel Straschil <axel@straschil.com>
1 sibling, 0 replies; 12+ messages in thread
From: PFC @ 2005-04-03 18:09 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; pgsql-sql
Use the ltree datatype !
It's made specifically for this purpose.
http://www.sai.msu.su/~megera/postgres/gist/
On Sun, 03 Apr 2005 12:13:48 +0200, Axel Straschil <axel@straschil.com>
wrote:
> 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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 18:11 PFC <lists@boutiquenumerique.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 12+ messages in thread
From: PFC @ 2005-04-03 18:11 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; pgsql-sql
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
On Sun, 03 Apr 2005 19:26:03 +0200, Axel Straschil <axel@straschil.com>
wrote:
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 18:18 Oleg Bartunov <oleg@sai.msu.su>
parent: Axel Straschil <axel@straschil.com>
0 siblings, 1 reply; 12+ messages in thread
From: Oleg Bartunov @ 2005-04-03 18:18 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; +Cc: pgsql-sql
On Sun, 3 Apr 2005, Axel Straschil wrote:
> 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?
I have no experience with those beasts, but what's wrong just untar source,
configure and compile by hand ?
>
> Thanks, AXEL.
>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 18:34 Axel Straschil <axel@straschil.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 18:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 19:03 Axel Straschil <axel@straschil.com>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 19:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 19:24 Oleg Bartunov <oleg@sai.msu.su>
parent: Axel Straschil <axel@straschil.com>
0 siblings, 1 reply; 12+ messages in thread
From: Oleg Bartunov @ 2005-04-03 19:24 UTC (permalink / raw)
To: Axel Straschil <axel@straschil.com>; +Cc: pgsql-sql
On Sun, 3 Apr 2005, Axel Straschil wrote:
> 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?
you need to load ltree into your database !
psql yourdb < ltree.sql
use 'locate ltree.sql' to find if ltree is installed
>
> Thanks,
> AXEL.
>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 19:57 PFC <lists@boutiquenumerique.com>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 2 replies; 12+ messages in thread
From: PFC @ 2005-04-03 19:57 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; Axel Straschil <axel@straschil.com>; +Cc: pgsql-sql
> you need to load ltree into your database !
> psql yourdb < ltree.sql
>
> use 'locate ltree.sql' to find if ltree is installed
Yeah, I remember now having to do that, but the binary module definitely
was here without having to do anything besides "emerge postgresql" :
peufeu@nyuu peufeu $ locate ltree.so
/usr/lib/postgresql/ltree.so
peufeu@nyuu peufeu $ locate ltree.sql
/usr/share/postgresql/contrib/ltree.sql
So just :
psql yourdb < /usr/share/postgresql/contrib/ltree.sql
And you should be OK.
Really this module is amazing. You can put a trigger on the table so
that, for instance, the path is created automatically from the element
name and the parent_id, that kind of things. I remember search was also
really fast and you can use complex queries without CONNECT BY...
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 20:36 Axel Straschil <axel@straschil.com>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 20:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to store directory like structures?
@ 2005-04-03 20:39 Axel Straschil <axel@straschil.com>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 12+ messages in thread
From: Axel Straschil @ 2005-04-03 20:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2005-04-03 20:39 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-03 10:13 How to store directory like structures? Axel Straschil <axel@straschil.com>
2005-04-03 17:00 ` Oleg Bartunov <oleg@sai.msu.su>
2005-04-03 17:26 ` Axel Straschil <axel@straschil.com>
2005-04-03 18:18 ` Oleg Bartunov <oleg@sai.msu.su>
2005-04-03 18:34 ` Axel Straschil <axel@straschil.com>
2005-04-03 18:11 ` PFC <lists@boutiquenumerique.com>
2005-04-03 19:03 ` Axel Straschil <axel@straschil.com>
2005-04-03 19:24 ` Oleg Bartunov <oleg@sai.msu.su>
2005-04-03 19:57 ` PFC <lists@boutiquenumerique.com>
2005-04-03 20:36 ` Axel Straschil <axel@straschil.com>
2005-04-03 20:39 ` Axel Straschil <axel@straschil.com>
2005-04-03 18:09 ` PFC <lists@boutiquenumerique.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox