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?
