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
>
>