Results of stored procedures in WHERE clause

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?

Responses

Browse pgsql-general by date

  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"