pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Tree structure
5+ messages / 3 participants
[nested] [flat]

* Tree structure
@ 2000-09-11 12:27 Trewern, Ben <Ben.Trewern@mowlem.com>
  2000-09-11 16:25 ` Re: Tree structure Albert REINER <areiner@tph.tuwien.ac.at>
  2000-09-11 16:30 ` Re: Tree structure Zeljko Trogrlic <zeljko@technologist.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 12:27 Tree structure Trewern, Ben <Ben.Trewern@mowlem.com>
@ 2000-09-11 16:25 ` Albert REINER <areiner@tph.tuwien.ac.at>
  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 12:27 Tree structure Trewern, Ben <Ben.Trewern@mowlem.com>
@ 2000-09-11 16:30 ` Zeljko Trogrlic <zeljko@technologist.com>
  2000-09-12 10:51   ` Re: Tree structure Zeljko Trogrlic <zeljko@technologist.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
&quot;ID&quot; and &quot;PARENT_ID&quot;. 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.&nbsp; Something like a
directory structure is what I'm aiming for.&nbsp; 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-11 12:27 Tree structure Trewern, Ben <Ben.Trewern@mowlem.com>
  2000-09-11 16:30 ` Re: Tree structure Zeljko Trogrlic <zeljko@technologist.com>
@ 2000-09-12 10:51   ` 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