postgresql Postgres:在插入或删除新记录时更新表排序

b5lpy0ml  于 2023-01-08  发布在  PostgreSQL
关注(0)|答案(1)|浏览(170)

我尝试排序表自动指定行每次一个新的记录被添加(或删除或更新)。
为此,我创建了一个函数

CREATE FUNCTION pid_cluster_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
     
     cluster verbose public.pid using pid_idx;
END;
$$

并添加触发器

CREATE trigger pid_cluster_trigger 
    after INSERT or update or DELETE on public.pid  
    FOR EACH row  
    execute procedure pid_cluster_function();

但加上一条记录

INSERT INTO public.pid (pid,pid_name) VALUES ('111','new 111');

我收到这样的错误

SQL Error [55006]: ERROR: cannot CLUSTER "pid" because it is being used by active queries in this session
  Where: SQL statement "cluster verbose public.pid using pid_idx"
PL/pgSQL function pid_cluster_function() line 5 at SQL statement

出现此错误的原因是什么?或者是否可以通过其他方式添加或修改记录来实现排序?

yc0p9oo0

yc0p9oo01#

好的,谢谢大家,在评论中,我看到我的想法并不聪明=)

相关问题