From: | Gordon <gordon(dot)mcvey(at)ntlworld(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Results of stored procedures in WHERE clause |
Date: | 2008-05-20 16:02:36 |
Message-ID: | cccd6edc-2491-4b3d-b981-c8b9de4fd8b2@m44g2000hsc.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2008-05-20 16:17:03 | Re: psql: FATAL: Ident authentication failed for user"postgres" |
Previous Message | Karsten Hilbert | 2008-05-20 15:51:06 | Re: psql: FATAL: Ident authentication failed for user "postgres" |