Re: Joe Celko Function

Great I can use it to something else, but its not resolve my problem as this
function delete one node and close one gap in the tree ( if I understood it
well ) while I wanted to create function that all it do is close gaps ( some
times big & multiplies ) that are created when I drop branches and not just
one node.

> Ben-Nes Michael wrote:
> > Hi All
> >
> > Im trying to build set of function too handle nested tree structure, so
I
> > used Joe Celco (SQL 4 Smarties).
> >
> > I have some problem migrating one of his function to plpgsql function
> >
>
> You must realize that the code he gave is pseudo-code, not real code. I
have the exact function you need.
>
> Here's the drop node function....my nested set table is called 'entity' so
just substitute your own table name.
> Each node of my tree has a unique ID 'entity_id' so this function takes in
as a parameter that unique ID to know
> which node to delete. You may need to alter that logic slightly depending
on how your own table works.
>
> (Now that I look at it the variable dropentity_id may not be necessary)
>
> create function dropentity(int4) returns int4 as '
> DECLARE
> dropentity_id int4;
> droplft int4;
> droprgt int4;
> BEGIN
> select entity_id, lft, rgt
> into dropentity_id, droplft, droprgt
> from entity
> where entity_id = $1;
>
> delete from entity
> where lft between droplft and droprgt;
>
> update entity
> set lft = case when lft > droplft
> then lft - (droprgt - droplft + 1)
> else lft end,
> rgt = case when rgt > droplft
> then rgt - (droprgt - droplft + 1)
> else rgt end;
> return 0;
> END;
> ' language 'plpgsql';
>
> Enjoy,
> Fran
>
>