Hello everyone,
I have a system (simplified for this example) with the following two tables:
TABLE1 id::int8 containerId::int8 containerType::varchar(100) moreInfo::text
TABLE2 id::int8 containerId::int8 containerType::varchar(100) otherInfo::text
Now, the rows of TABLE2 are children of TABLE1 objects. Therefore, an entry
in TABLE1 and TABLE2 looks like that:
TABLE1:
id containerId containerType moreInfo
-------------------------------------------------------------------------
1 0 null 'foo'
TABLE2:
id containerId containerType otherInfo
-------------------------------------------------------------------------
1 1 TABLE1 'bar'
2 1 TABLE1 'more bar'
3 2 TABLE2 're: more bar'
Since in this case TABLE1's row 1 is a root object it has no containerType
and containerId (it has no parent). The first 2 TABLE2 rows are sub-objects
of TABLE1, therefore TABLE1 is referenced there. But Tables (objects) can
also reference themselves, like it would be the case in a message board.
Now my problem:
-------------------------
I need to figure out the object-tree for any row in the system (of course I
do not only have 2 levels like in this example). The preferred output
should look like this (or similar):
level containerType containerId
---------------------------------------------- 1 TABLE1 1 2 TABLE2 2 3
TABLE2 3
This would be the object tree for row 3 in TABLE2. I am pretty sure one
would need to use pg/PLSQL or something similar, if that is possible at
all. I could solve the problem in my programming language with a recursive
function, but it would be VERY expensive, since I would need to make n DB
requests (n --> number of levels).
If you know a better way to represent a system like that generically, let
me know. I thought about making a base table with the fields
(containerType, containerId) and then inherit that table, but that did not
get me much further.
THANKS A LOT FOR YOUR TIME IN ADVANCE!!!
Regards,
Stephan
--
Stephan Richter
CBU - Physics and Chemistry Student
Web2k - Web Design/Development & Technical Project Management
