pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing a self referencing table
2+ messages / 2 participants
[nested] [flat]
* using a self referencing table
@ 2000-07-18 16:05 drfrog@smartt.com
2000-07-19 00:19 ` Re: using a self referencing table andrew <TheDog@TheSoftwareSmith.Com.Au>
0 siblings, 1 reply; 2+ messages in thread
From: drfrog@smartt.com @ 2000-07-18 16:05 UTC (permalink / raw)
To: pgsql-sql
i have a table with this structure
name (varchar)|category id (int4)|parent category id (int4)|leaf node
(bool)
im trying to make a perl script that should tree the info
parent cat
subcat
subcat2
subcat2
subcat
subcat2
...
but im having troubles wrapping my head around this
im using the Pg modules to access postgres
if anyone has any suggestions please lemme know thanks!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: using a self referencing table
2000-07-18 16:05 using a self referencing table drfrog@smartt.com
@ 2000-07-19 00:19 ` andrew <TheDog@TheSoftwareSmith.Com.Au>
0 siblings, 0 replies; 2+ messages in thread
From: andrew @ 2000-07-19 00:19 UTC (permalink / raw)
To: drfrog@smartt.com; pgsql-sql
drfrog@smartt.com wrote:
> i have a table with this structure
>
> name (varchar)|category id (int4)|parent category id (int4)|leaf node
> (bool)
>
> im trying to make a perl script that should tree the info
>
> parent cat
> subcat
> subcat2
> subcat2
> subcat
> subcat2
> ...
>
> but im having troubles wrapping my head around this
>
> im using the Pg modules to access postgres
>
> if anyone has any suggestions please lemme know thanks!
The trick is to be able to sort all the elements of the hierarchy so
that they come out in the right order, that is, grouped by parent, and
then indent them.
You can do this with two extra redundant fields, clevel int and csort
text. Every time you insert a node in the tree, include the calculated
level of the node (i.e. clevel = parent->clevel + 1) and a string which
ensures that the children of a given node are grouped together (i.e.
csort = parent->csort + category id::text). Probably best to left pad
the category id with an appropriate number of zeroes for this
calculation.
When you retrieve the nodes, order by csort, and convert clevel into the
appropriate number of indents.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-07-19 00:19 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-18 16:05 using a self referencing table drfrog@smartt.com
2000-07-19 00:19 ` andrew <TheDog@TheSoftwareSmith.Com.Au>
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