pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTree structure
5+ messages / 3 participants
[nested] [flat]
* Tree structure
@ 2000-09-11 12:27 Trewern, Ben <Ben.Trewern@mowlem.com>
0 siblings, 2 replies; 5+ messages in thread
From: Trewern, Ben @ 2000-09-11 12:27 UTC (permalink / raw)
To: pgsql-sql
Anybody know how to make a tree structure using related tables using
Postgres. Something like a directory structure is what I'm aiming for. I'm
sure there is an easy way but I'm having probs.
Any help would be appreciated.
Ben.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tree structure
@ 2000-09-11 16:25 Albert REINER <areiner@tph.tuwien.ac.at>
parent: Trewern, Ben <Ben.Trewern@mowlem.com>
1 sibling, 0 replies; 5+ messages in thread
From: Albert REINER @ 2000-09-11 16:25 UTC (permalink / raw)
To: Trewern, Ben <Ben.Trewern@mowlem.com>; pgsql-sql
On Mon, Sep 11, 2000 at 01:27:48PM +0100, Trewern, Ben wrote:
> Anybody know how to make a tree structure using related tables using
> Postgres. Something like a directory structure is what I'm aiming for. I'm
> sure there is an easy way but I'm having probs.
I am not quite sure whether this is what you are thinking about:
create table tree (
id int4,
parentid int4,
data text)
A structure like
a
|__b
|__c
can be achieved by
insert into tree (id, parentid, data) values (1, NULL, 'a');
insert into tree (id, parentid, data) values (2, 1, 'b');
insert into tree (id, parentid, data) values (3, 1, 'c');
Probably you'll also want to use a sequence for the ids, and to
declare indices on id, parentid etc.
>
> Any help would be appreciated.
>
> Ben.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tree structure
@ 2000-09-11 16:30 Zeljko Trogrlic <zeljko@technologist.com>
parent: Trewern, Ben <Ben.Trewern@mowlem.com>
1 sibling, 1 reply; 5+ messages in thread
From: Zeljko Trogrlic @ 2000-09-11 16:30 UTC (permalink / raw)
To: Trewern, Ben <Ben.Trewern@mowlem.com>; pgsql-sql
<html>
Normally it's done with self-relation. You need a table with
"ID" and "PARENT_ID". PARENT_ID will contain null for
root level and other row's ID for children. One warning: going through
such tree could be slow. I don't know is there anything PostgreSQL
specific that might help. I know that Oracle have a special tree
command.<br>
<br>
At 14:27 11.9.2000 , Trewern, Ben wrote: <br>
<br>
<font size=2><blockquote type=cite cite>Anybody know how to make a tree
structure using related tables using Postgres. Something like a
directory structure is what I'm aiming for. I'm sure there is an
easy way but I'm having probs.<br>
</font><br>
Any help would be appreciated. <br>
<br>
<font size=2>Ben.</font> </blockquote><br>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Tree structure
@ 2000-09-12 10:51 Zeljko Trogrlic <zeljko@technologist.com>
parent: Zeljko Trogrlic <zeljko@technologist.com>
0 siblings, 0 replies; 5+ messages in thread
From: Zeljko Trogrlic @ 2000-09-12 10:51 UTC (permalink / raw)
To: kovacsz <zoli@pc10.radnoti-szeged.sulinet.hu>; +Cc: pgsql-sql
Thanks for your offer, I'll be very interested to see how you did that. I
also found very interesting solution at
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
At 11:22 12.9.2000 , kovacsz wrote:
>We are using tree structures (and partially ordered trees) with
>PostgreSQL. After the beginning success we got into speed problems. With
>special plus tables, triggers and functions now we have a reasonably fast
>database which contains about 5-10 tree structures. If you are interested,
>I may send you some of these part of our database. But, PostgreSQL "from
>base" doesn't support tree structures, neither recursions (like in SQL3),
>as far as I know (see TODO list).
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Tree structure
@ 2000-09-12 14:52 Trewern, Ben <Ben.Trewern@mowlem.com>
0 siblings, 0 replies; 5+ messages in thread
From: Trewern, Ben @ 2000-09-12 14:52 UTC (permalink / raw)
To: 'kovacsz' <zoli@pc10.radnoti-szeged.sulinet.hu>; pgsql-sql
Any details you can supply would be appreciated. I have got a table at the
moment as:
id | group_name | parentid
-------------------------------
1 | Main |
2 | Group |
3 | User |
4 | Group1 | 1
5 | Group2 | 1
6 | Group2-1 | 5
Just for testing. I can see how it should work but the client side code is
defeating me at the moment. I'm working in delphi with winzeos (cracking
components by the way). Anyone done this.
Thanks for any help
Ben Trewern
> -----Original Message-----
> From: kovacsz [mailto:zoli@pc10.radnoti-szeged.sulinet.hu]
> Sent: 12 September 2000 10:23
> To: Zeljko Trogrlic
> Cc: Trewern, Ben; 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] Tree structure
>
>
> We are using tree structures (and partially ordered trees) with
> PostgreSQL. After the beginning success we got into speed
> problems. With
> special plus tables, triggers and functions now we have a
> reasonably fast
> database which contains about 5-10 tree structures. If you
> are interested,
> I may send you some of these part of our database. But,
> PostgreSQL "from
> base" doesn't support tree structures, neither recursions
> (like in SQL3),
> as far as I know (see TODO list).
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-09-12 14:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-11 12:27 Tree structure Trewern, Ben <Ben.Trewern@mowlem.com>
2000-09-11 16:25 ` Albert REINER <areiner@tph.tuwien.ac.at>
2000-09-11 16:30 ` Zeljko Trogrlic <zeljko@technologist.com>
2000-09-12 10:51 ` Zeljko Trogrlic <zeljko@technologist.com>
2000-09-12 14:52 RE: Tree structure Trewern, Ben <Ben.Trewern@mowlem.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