On May 20, 5:02 pm, Gordon <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
