Thread: Results of stored procedures in WHERE clause
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?
> > 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: > > 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? > I don't think you can reference an alias in the where clause. You'll have to repeat it, like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_id) AS itm_root FROM cms_v_items WHERE getroot (cms_v_items.itm_id) = ?; Don't worry, I think with the function marked STABLE, postgresql is smart enough not to call it twice. I think you could further optimize your function doing something like this: SELECT cms_v_items.* , getroot (cms_v_items.itm_parent) AS itm_root FROM cms_v_items WHERE (itm_parent = ? OR getroot (cms_v_items.itm_parent) = ?; This will save one loop. Keep in mind, both queries will perform the getroot() function call for every single row in cms_v_items. You may want to experiment with a function that takes the root ID as a parameter and returns an array or a rowset, of just the items beneath that root. Then you'd use that function in your query by joining to the results or using "= ANY". This might be faster: SELECT * from FROM cms_v_items WHERE itm_id = ANY(item_in_root(?));
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Gordon > Sent: Tuesday, May 20, 2008 11:03 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Results of stored procedures in WHERE clause > > 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: ... I would highly recommend checking out the ltree contrib module. It will make this task much easier, as long as you are not locked into the current database design. Manually performing multi-level parent/child relationships on a table can become quite painful. You will probably get faster results using ltree also due to the fact that you can perform what you want with one query instead of looping through multiple queries (very important if your tree gets big). http://www.sai.msu.su/~megera/postgres/gist/ltree/ Justin Pasher
I'm assuming you are having problems because the tree structure allows
for n levels and you need to get all of the child records.
I am guessing you mean this:
create table cms_items
(itm_id integer primary key not null,
itm_parent integer default 0 not null,
url varchar(100) not null);
insert into cms_items values (1, 0, 'postgresql.org');
insert into cms_items values (2, 1, 'foo');
insert into cms_items values (3, 1, 'bar');
insert into cms_items values (4, 0, 'cnn.com');
insert into cms_items values (5, 2, 'foo2');
insert into cms_items values (6, 5, 'foo3');
create or replace function fn_get_root (p_itm_id integer) returns setof
cms_items as
$$
declare
v_rec cms_items;
v_rec2 cms_items;
i integer := 0;
v_last_itm_id cms_items.itm_id%type;
begin
<<outside_loop>>
for v_rec in select * from cms_items where itm_parent = p_itm_id loop
return next v_rec;
<<inside_loop>>
while i is not null loop
i := i + 1;
if i = 1 then
v_last_itm_id := v_rec.itm_id;
end if;
select * into v_rec2 from cms_items where itm_parent =
v_last_itm_id;
if v_rec2.itm_id is not null then
return next v_rec2;
else
i := null;
end if;
v_last_itm_id := v_rec2.itm_id;
end loop inside_loop;
end loop outside_loop;
end;
$$
language 'plpgsql';
select * from fn_get_root(1);
2;1;"foo"
5;2;"foo2"
6;5;"foo3"
3;1;"bar"
It gets the direct child records and then it also gets the child's child
(foo2) and then the child's child's child (foo3). It will go all of the
way through the hierarchy too.
Jon
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Gordon
> Sent: Tuesday, May 20, 2008 11:03 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Results of stored procedures in WHERE clause
>
> 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general