I am implementing some set-based trees in a table and I've been writing
some functions to manipulate (delete branch, insert node, move branch)
them and I've run into a problem with the move_branch function.
When I try a select move_tree(13, 25, 0); to move the branch labeled g
to be a child of the branch labeled i I get an ERROR: Cannot insert a
duplicate key into unique index plan_items_pkey. Why is this happening,
I thought that the primary key constraints should be checked AFTER the
update is finished, am I wrong? Is there some other way to do this in a
single update or will I have to break it up? Can I defer constraint
checking on unique indexes? Any help would be appreciated.
I'll simplify my structure a little here:
create table plan_items (
lft int4 not null,
rgt int4 not null,
name char(50) not null,
primary key (lft),
check (lft < rgt),
check (lft > 0),
check (rgt > 0)
);
copy plan_items from stdin;
1 28 a
2 11 b
3 8 e
4 5 j
6 7 k
9 10 f
12 21 c
13 20 g
14 15 l
16 17 m
18 19 n
22 27 d
23 24 h
25 26 i
\.
And now for the function:
create function move_tree(integer, integer, integer)
returns integer as '
declare
p_node alias for $3;
p_parent alias for $4;
p_brother alias for $5;
droplft plan_items.lft%TYPE;
droprgt plan_items.rgt%TYPE;
newpos plan_items.lft%TYPE;
begin
select rgt
into newpos
from plan_items
where lft = p_parent;
if p_brother > 0 then
select rgt + 1
into newpos
from plan_items
where lft = p_brother;
end if;
select lft, rgt
into droplft, droprgt
from plan_items
where lft = p_node;
/* reorder nodes in position ??? */
update plan_items set
lft = case
when lft between droplft and droprgt then
lft + newpos - droprgt - 1
when (lft < droplft) and (lft >= newpos) then
lft + (droprgt - droplft + 1)
when (lft > droprgt) and (lft < newpos) then
lft - (droprgt - droplft + 1)
else lft end,
rgt = case
when rgt between droplft and droprgt then
rgt + newpos - droprgt - 1
when (rgt < droplft) and (rgt >= newpos) then
rgt + (droprgt - droplft + 1)
when (rgt > droprgt) and (rgt < newpos) then
rgt - (droprgt - droplft + 1)
else rgt end;
end;
' language 'plpgsql';
--
ashley clark
