> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Gordon > Sent: Tuesday, May 20, 2008 11:03 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: [GENERAL] Results of stored procedures in WHERE clause > > I have a table representing tree structures of pages on a website. > they have an itm_id column (integer key) and an itm_parent column > (pointer to item's parent node). Any item with an itm_parent of 0 is > a root node, representing a website. Anything with a non-zero parent > is a non-root node representing a folder or document in a website. > > I need to be able to do queries that restrict my result set to items > belonging to a specified site and ignore all nodes that belong to > different sites. To determine the ID of the site an item belongs to I > wrote a stored procedure: ... I would highly recommend checking out the ltree contrib module. It will make this task much easier, as long as you are not locked into the current database design. Manually performing multi-level parent/child relationships on a table can become quite painful. You will probably get faster results using ltree also due to the fact that you can perform what you want with one query instead of looping through multiple queries (very important if your tree gets big). http://www.sai.msu.su/~megera/postgres/gist/ltree/ Justin Pasher
