Re: Joe Celko Function

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