在PostgreSQL中排序删除 [英] Delete with sorting in PostgreSQL

问题描述

我正在尝试按特定顺序删除行。我尝试了帖子如何在PostgreSQL中通过排序删除固定数量的行？，但是没有任何效果。我知道它们不起作用，因为我在删除操作上有一个触发器，并且看到哪个被删除了，并且它从未按照我想要的顺序执行。

I am trying to delete rows in a specific order. I tried the suggestions in the post How do I delete a fixed number of rows with sorting in PostgreSQL?, but nothing worked. I know they did not work because I had a trigger on delete and seeing which one is deleted and it is never doing it in the order that I want it to.

我编写的测试是这样的：

The tests that I wrote are like this:

create table test1 (
    th text,
    co text,
    ty text);

CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$

DECLARE modified_row test1%ROWTYPE;
BEGIN

    IF TG_OP = 'DELETE' THEN
    modified_row := OLD;
ELSE
    modified_row := NEW;
END IF;
raise notice 'opearation: %, co: %', TG_OP, modified_row.co;
    RETURN modified_row;
END;
$$;

CREATE TRIGGER trigger_test1
BEFORE DELETE OR INSERT on test1
FOR EACH ROW
EXECUTE PROCEDURE testfunc();

insert into test1 (th, co, ty) values ('t1', 'c1', 'p'), ('t2', 'c2','p'), ('t2', 'c3','p'), ('t2', 'c4','p');
delete from test1 where ctid  in (select ctid from test1 order by co desc);

上面的delete语句是以上帖子中投票率最高且被接受的答案。但是对我来说，输出始终是：

The delete statement above is the highest voted and accepted answer in the above post. But for me, the output has always been:

注意：运维：DELETE，co：c1

NOTICE: opearation: DELETE, co: c1

注意：操作：DELETE，co：c2

NOTICE: opearation: DELETE, co: c2

注意：操作：DELETE，co：c3

NOTICE: opearation: DELETE, co: c3

注意：操作：DELETE，co：c4

NOTICE: opearation: DELETE, co: c4

表示删除操作不是按照我要执行的'co'的降序进行的。我在这里遗漏了什么吗？

which indicates that the deletes are not done in desc order of 'co' that I am trying to do. Am I missing something here?

注意：忘记插入顺序，我只关心删除顺序。而且我不能在上面的帖子中问这个问题，因为我没有足够的声誉来发表评论。

NOTE: forget about the insert order, I am only concerned about the delete order. And I could not ask this question in the above post because I do not have enough reputation to put a comment.

推荐答案

我能想到的方法是循环删除。
以下是使用游标的示例：

The only way I can think of is delete in a loop. Here's an example using a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;
    DECLARE cur CURSOR FOR SELECT * FROM test1 ORDER BY co DESC FOR UPDATE;

BEGIN
    FOR test1_row IN cur 
    LOOP
        DELETE FROM test1 WHERE CURRENT OF cur;
    END LOOP;
END $$

没有光标的示例：

DO $$
DECLARE 
    test1_row test1%ROWTYPE;    
BEGIN
    FOR test1_row IN SELECT * FROM test1 ORDER BY co DESC
    LOOP
        DELETE FROM test1 WHERE th = test1_row.th AND co = test1_row.co AND ty = test1_row.ty;
    END LOOP;
END $$

注意：位置 DELETE 语句中的$ c>条件不可靠，因为 test1 表没有主键/唯一键。在其他情况下，您可以使用： WHERE id = row.id

Note: the WHERE condition in DELETE statement above is not reliable because test1 table does not have a primary/unique key. In other cases, you could just use: WHERE id = row.id

这篇关于在PostgreSQL中排序删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！