Re: Results of stored procedures in WHERE clause

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Gordon
> Sent: Tuesday, May 20, 2008 11:03 AM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general