另一个具有多个唯一索引的表：
create table utest(id integer, position integer not null, unique(id, position));test=# d utest Table "public.utest" Column | Type | Modifiers ----------+---------+----------- id | integer | position | integer | not nullIndexes: "utest_id_key" UNIQUE, btree (id, "position")
一些数据：
insert into utest(id, position) select generate_series(1,3), 1;insert into utest(id, position) select generate_series(1,3), 2;insert into utest(id, position) select generate_series(1,3), 3;test=# select * from utest order by id, position; id | position ----+---------- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3(9 rows)
我创建了一个以正确的顺序更新位置值的过程：
create or replace function update_positions(i integer, p integer) returns void as $$declare temprec record;begin for temprec in select * from utest u where id = i and position >= p order by position desc loop raise notice 'Id = [%], Moving % to %', i, temprec.position, temprec.position+1; update utest set position = position+1 where position=temprec.position and id = i; end loop;end;$$ language plpgsql;
一些测试：
test=# select * from update_positions(1, 2);NOTICE: Id = [1], Moving 3 to 4NOTICE: Id = [1], Moving 2 to 3 update_positions ------------------(1 row)test=# select * from utest order by id, position; id | position ----+---------- 1 | 1 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3(9 rows)
希望能帮助到你。
