From: | Fran Fabrizio <ffabrizio(at)mmrd(dot)com> |
---|---|
To: | Ben-Nes Michael <miki(at)canaan(dot)co(dot)il> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Joe Celko Function |
Date: | 2002-05-01 13:17:20 |
Message-ID: | 3CCFEAE0.6050202@mmrd.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ben-Nes Michael | 2002-05-01 13:25:23 | Re: Joe Celko Function |
Previous Message | Ben-Nes Michael | 2002-05-01 11:58:50 | Joe Celko Function |