Date: Thu, 09 Sep 2004 11:43:33 +1000
From: Terence Kearns <terencek@isd.canberra.edu.au>
Subject: check for circular references in a 2-table heirachy
To: pgsql-sql@postgresql.org
Message-id: <413FB545.9090100@isd.canberra.edu.au>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2)
	Gecko/20040803 MultiZilla/1.6.3.2d

Does anyone know how I could check to ensure circular references are no 
created in my 2 table heirachy structure?

Here are the tables

albums              album_relations
+------------+     +-----------------+
| album_id   |     | parent_album_id |
| title      |     | child_album_id  |
+------------+     +-----------------+

This structure allows an album to have multiple parents.
I have written a recursive function (on a PHP class) which creates an 
XML tree structure based on a 2D array result set**. It has a depth 
checker so you can set the limit of recursion.

This is all well and good, but I want to create a PLPG/SQL trigger on 
the album_relations table to ensure that I won't have to throw the 
"depth limit exceeded" exception due to circular references.

Any ideas would be appreciated.


** 2D array result set is built from
SELECT a.*,r.parent_album_id
FROM album_relations r
     RIGHT JOIN albums a
         ON a.album_id = r.child_album_id