pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




Message-ID: <5.0.2.1.0.20010223161900.036fe650@198.78.130.6>
Permalink:  ../5.0.2.1.0.20010223161900.036fe650@198.78.130.6/
Also on:    postgresql.org/message-id/5.0.2.1.0.20010223161900.036fe650@198.78.130.6

 · 

reply

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