Results of stored procedures in WHERE clause

Поиск
Список
Период
Сортировка
От Gordon
Тема Results of stored procedures in WHERE clause
Дата
Msg-id cccd6edc-2491-4b3d-b981-c8b9de4fd8b2@m44g2000hsc.googlegroups.com
обсуждение исходный текст
Ответы Re: Results of stored procedures in WHERE clause
Re: Results of stored procedures in WHERE clause
Re: Results of stored procedures in WHERE clause
Список pgsql-general
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?

В списке pgsql-general по дате отправления:

Предыдущее
От: iSteve
Дата:
Сообщение: Re: tsearch2 on-demand dictionary loading & using functions in tsearch2
Следующее
От: "Scott Marlowe"
Дата:
Сообщение: Re: quote in string