pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
using a self referencing table
2+ messages / 2 participants
[nested] [flat]

* using a self referencing table
@ 2000-07-18 16:05  drfrog@smartt.com
  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-19 00:19  andrew <TheDog@TheSoftwareSmith.Com.Au>
  parent: drfrog@smartt.com
  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