Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

SELECT 2


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

SELECT 2


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

CREATE PROCEDURE


call  addNodesFromNewNodes('games');
select * from tablename;

CALL




name
lft
rgt
level


games
2
3
2

gadgets
1
8
1

cartridge
4
5
2

console
6
7
2



SELECT 4