Adding New Nodes in Modified Preorder Tree Traversal - PostgreSQL

Question

I am having a postgresql table of approx 1500 rows and the model is “Modified Preorder Tree Traversal” structure based on Left and Right field. and i want to insert many rows in between but i am not sure how to calculate the level, rightindex and leftindex columns values for multiple rows when inserting in between of rows.

Data

I believe for a single row we can do achieve this by below code,

SELECT @myRight := rgt FROM tablename

UPDATE tablename SET rgt = rgt + 2 WHERE rgt > @myRight;

UPDATE tablename SET lft = lft + 2 WHERE lft > @myRight;


INSERT INTO tablename(name, lft, rgt)
    VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

But how can we do this for multiple updates for a large table.

Please let me know if any further information is needed from my end.

Answer 1

An example of a procedure which adds new items from the NewNodes table to the tablename

Setup

select * into tablename 
from(
  values
  ('gadgets',1,4, 1),
  ('games', 2,3, 2)
)t(name, lft, rgt, level);


select * into newNodes
from(
  values
  ('console'),
  ('cartridge')
)t(name);

The procedure adds new nodes after the node with the specified name at the same level.

create procedure addNodesFromNewNodes(posname varchar(10))
LANGUAGE plpgsql
as $$
declare
  pos int; lvl int;
  N int;
begin
  select rgt, level  into pos, lvl
  FROM tablename
  WHERE Name = posname;
  
  select count(*) into N
  from newNodes;
  
  update tablename 
  set lft = lft + 2*N
  where lft > pos;
  update tablename 
  set rgt = rgt + 2*N
  where rgt > pos;
  
  insert into tablename(name, lft, rgt, level)
  select t.Name, pos + 2 * rn - 1, pos + 2 * rn, lvl
  from (
     select Name, row_number() over(order by name) rn
     from NewNodes
  ) t;
end;
$$

db<>fiddle