From: Stephan Richter <srichter@cbu.edu>
To: pgsql-sql@postgresql.org
Subject: Recursive Query (need of PL/pgSQL?)
Date: Fri, 23 Feb 2001 16:40:08 -0600
Message-ID: <5.0.2.1.0.20010223161900.036fe650@198.78.130.6> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: srichter@cbu.edu
Subject: Re: Recursive Query (need of PL/pgSQL?)
In-Reply-To: <5.0.2.1.0.20010223161900.036fe650@198.78.130.6>
References: <5.0.2.1.0.20010223161900.036fe650@198.78.130.6>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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