Re: Results of stored procedures in WHERE clause

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Gordon
> Sent: Tuesday, May 20, 2008 11:03 AM
> To: pgsql-general(at)postgresql(dot)org
> 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