Re: Results of stored procedures in WHERE clause

On May 20, 5:02 pm, Gordon <gordon(dot)mc(dot)(dot)(dot)(at)ntlworld(dot)com> wrote:
> 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:
>
> CREATE OR REPLACE FUNCTION cms.getroot(node integer)
> RETURNS integer AS
> $BODY$DECLARE
> thisnode integer := node;
> thisparent integer := node;
> BEGIN
> WHILE thisparent != 0 LOOP
> SELECT itm_id, itm_parent
> INTO thisnode, thisparent
> FROM cms.cms_items
> WHERE itm_id = thisparent;
> END LOOP;
> RETURN thisnode;
> END;
> $BODY$
> LANGUAGE 'plpgsql' STABLE
> COST 100;
>
> 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?

Is what I'm trying to do even possible? I'm really struggling to find
much help with Google on this topic.