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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match