Postgres-simple-reordering-of-rows.md

Simple reordering of items in database table

The problem

Ability to reorder item based on users drag and drop of items in UI. Items can be drag and drop in as a single object or with This is not the most efficient way how to do this but it's working just fine.

-- Drop functions and data table if they already exists
drop function save_todo_float(_user_id int, _what text, _todo_id int);
drop function move_todo_float(_user_id int, _ids int[], _target_id int, _position text);
drop table todos_float;

-- Create table to keep user's todos
create table todos_float (
  id serial not null primary key,
  user_id int not null,
  prio float, -- priority is of type float and is ascendent
  what text not null,
  created timestamp default now()
);

-- Create save_todo_float function
create or replace function save_todo_float(_user_id int, _what text, _todo_id int default null::int)
returns setof todos_float
    language plpgsql
as
$$
    declare
        __last_prio float;
        __last_id int;
    begin
        if _todo_id is null then
            select max(prio) into __last_prio from todos_float where user_id = _user_id; -- find last priority for given _user_id
            
            -- insert new record with proper priority
            insert into todos_float(user_id, what, prio) values (_user_id, _what, coalesce(__last_prio, 0) + 1)
            returning id
            into __last_id;
        else
            update todos_float set what = _what where id = _todo_id
            returning id
            into __last_id;
        end if;
       
        return query
        select * from todos_float where id = __last_id;
    end
$$;


create or replace function move_todo_float (_user_id int, _ids int[], _target_id int, _position text)
returns setof todos_float
language plpgsql
as
$$
    declare
        __target_prio float;
    begin
        
        select prio
        into __target_prio
        from todos_float
        where user_id = _user_id and id = _target_id; -- check if user is trying to manipulate with own record
        
        if __target_prio is null then
            RAISE EXCEPTION 'Target id is out of user''s range';
        end if;
        
        update todos_float utf
        set prio = case when _position = 'before' then __target_prio - ((array_length(_ids, 1) * 0.0001 + 0.0001) - rownum * 0.0001)
            else __target_prio + rownum * 0.001 end -- add or remove 0.0001 to targets priority and keep original priorities of moved todos
        from (select id, row_number() over (order by prio asc) as rownum
            from todos_float
            where user_id = _user_id and id = ANY(_ids) order by prio) as tf
        where utf.id = tf.id;
       
        update todos_float utf set prio = rownum -- recalculate all all priorities to be ready for the next run
        from (select id, row_number() over (order by prio asc) as rownum
            from todos_float where user_id = _user_id) as tf
        where utf.id = tf.id;
        
        return query
        select *
        from todos_float
        where user_id = _user_id
        order by prio;
    end
$$;

Usage

select * from todos_float where user_id = 3 order by prio;
select * from move_todo_float(3, '{11,12}', 9, 'after'); -- you can move multiple todos at once
select * from save_todo_float(3, 'todo 8');