From: Terence Kearns <terencek@isd.canberra.edu.au>
To: pgsql-sql@postgresql.org
Subject: check for circular references in a 2-table heirachy
Date: Thu, 09 Sep 2004 11:43:33 +1000
Message-ID: <413FB545.9090100@isd.canberra.edu.au> (raw)
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terencek@isd.canberra.edu.au
Subject: Re: check for circular references in a 2-table heirachy
In-Reply-To: <413FB545.9090100@isd.canberra.edu.au>
* 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