Recursive Query (need of PL/pgSQL?)

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