> > 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: > > This returns the ID of the root node for non-root nodes, the node's > own ID for root-nodes and NULL for invalid IDs. > > I'm writing a query to do document searching (the version given is > simplified to the problem in hand). > > SELECT cms_v_items.* , > getroot (cms_v_items.itm_id) AS itm_root > FROM cms_v_items > WHERE itm_root = ?; > > I was hoping this query would return a set of items that had the same > root node. Instead it throws an error, column itm_root does not > exist. > > I'm obviously doing something wrong here, but what? > I don't think you can reference an alias in the where clause. You'll have to repeat it, like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_id) AS itm_root FROM cms_v_items WHERE getroot (cms_v_items.itm_id) = ?; Don't worry, I think with the function marked STABLE, postgresql is smart enough not to call it twice. I think you could further optimize your function doing something like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_parent) AS itm_root FROM cms_v_items WHERE (itm_parent = ? OR getroot (cms_v_items.itm_parent) = ?; This will save one loop. Keep in mind, both queries will perform the getroot() function call for every single row in cms_v_items. You may want to experiment with a function that takes the root ID as a parameter and returns an array or a rowset, of just the items beneath that root. Then you'd use that function in your query by joining to the results or using "= ANY". This might be faster: SELECT * from FROM cms_v_items WHERE itm_id = ANY(item_in_root(?));
