>
> 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(?));